JamHitz Productions

Examples of Common Queries

Here follows examples of how to solve some common problems with MySQL.

Some of the examples use the table shop to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (item, trader) is a primary key for the records.

You can create the example table as:

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));


INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

Okay, so the example data is:

SELECT * FROM shop

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The maximum value for a column

``What's the highest item number?''

SELECT MAX(article) AS article FROM shop

+---------+
| article |
+---------+
|       4 |
+---------+

The row holding the maximum of a certain column

``Find number, dealer, and price of the most expensive article.''

In ANSI SQL this is easily done with a sub-query:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop)

In MySQL (which does not yet have sub-selects), just do it in two steps:

1. Get the maximum price value from the table with a SELECT statement.
2. Using this value compile the actual query:

SELECT article, dealer, price
FROM   shop
WHERE  price=19.95

Another solution is to sort all rows descending by price and only get the first row using the MySQL specific LIMIT clause:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1

Note: If there are several most expensive articles (e.g. each 19.95) the LIMIT solution shows only one of them!

Maximum of column: per group: only the values

``What's the highest price per article?''

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

The rows holding the group-wise maximum of a certain field

``For each article, find the dealer(s) with the most expensive price.''

In ANSI SQL, I'd do it with a sub-query like this:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)

In MySQL it's best do it in several steps:

1. Get the list of (article,maxprice).
2. For each article get the corresponding rows which have the stored maximum price.

This can easily be done with a temporary table:

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES article read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

If you don't use a TEMPORARY table, you must also lock the 'tmp' table.

``Can it be done with a single query?''

Yes, but only by using a quite inefficient trick that I call the "MAX-CONCAT trick'':

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The last example can of course be made a bit more efficient by doing the splitting of the concatenated column in the client.

Using foreign keys

You don't need foreign keys to join 2 tables.

The only thing MySQL doesn't do is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from table with a foreign key definition. If you use your keys like normal, it'll work just fine!

CREATE TABLE persons (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirts (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
    PRIMARY KEY (id)
);

INSERT INTO persons VALUES (NULL, 'Antonio Paz');

INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());

SELECT * FROM persons;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

SELECT s.* FROM persons p, shirts s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

Searching on two keys

MySQL doesn't yet optimize when you search on two different keys combined with OR (Searching on one key with different OR parts is optimized quite good):

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR  field2_index = '1'

The reason is that we haven't yet had time to come up with an efficient way to handle this in the general case. (The AND handling is in comparison now completely general and works very well).

For the moment you can solve this very efficently by using a TEMPORARY table; This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

The above way to solve this query is in effect an UNION of two queries.