MySQL 5.4 Reference Manual :: 3 Tutorial :: 3.3 Creating and Using a Database :: 3.3.4 Retrieving Information from a Table :: 3.3.4.9 Using More Than one Table

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

Previous / Next / Up / Table of Contents

User Comments

Posted by Steve Seliquini on November 30 2003 8:46am

[Delete] [Edit]

Depending on when this query was run, Bowser might not be available for mating. :) In this case we can add additional criteria to the where clause to ensure that the animal is actually alive to perform.

select p1.name, p1.gender, p2.name, p2.gender, p1.species
from pet as p1, pet as p2
where p1.species = p2.species and p1.gender = 'f' and p2.gender = 'm'
and p1.death is null and p2.death is null;

+--------+--------+-------+--------+---------+
| name   | gender | name  | gender | species |
+--------+--------+-------+--------+---------+
| Fluffy | f      | Claws | m      | cat     |
| Buffy  | f      | Fang  | m      | dog     |
+--------+--------+-------+--------+---------+

2 rows in set (0.00 sec)

mysql>

Posted by [name withheld] on December 30 2003 12:55pm

[Delete] [Edit]

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m"
-> AND p1.death IS NULL and p2.death IS NULL;

+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

2 rows in set (0.00 sec)

mysql>


The above follows the naming convention and syntax of the tutorial.

Posted by Allen Moore on December 2 2004 1:52am

[Delete] [Edit]

Windows users, if your LOAD DATA INFILE... command mangles your input, remember to try adding ...LINES TERMINATED BY '\r\n'.

Posted by Steve Lawson on January 22 2005 5:42pm

[Delete] [Edit]

Here's a query that produces a more succinct version of the "Breeding Pairs" table (that ignores the fact that poor Bowser is dead):

SELECT p1.species as Species, p1.name AS Female, p2.name AS Male
FROM pets AS p1, pets AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'

+---------+--------+--------+
| Species | Female |  Male  |
+---------+--------+--------+
|   cat   | Fluffy | Claws  |
|   dog   | Buffy  | Fang   |
|   dog   | Buffy  | Bowser |
+---------+--------+--------+

Here it is as a LEFT JOIN:

SELECT p1.species, p1.name AS Male, p2.name AS Female
FROM pets AS p1
LEFT JOIN pets AS p2 ON p1.species = p2.species
WHERE p1.sex = 'm' AND p2.sex = 'f'

and with the dead pet filter:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pets AS p1, pets AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL

Posted by Mario Vargas on September 25 2006 3:40pm

[Delete] [Edit]

The above queries by Steve, using the naming convention in the tutorial to avoid confusion (Steve's table is named "pets" and not "pet"):

Steve's concise example:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm';

+---------+--------+--------+
| Species | Female | Male   |
+---------+--------+--------+
| cat     | Fluffy | Claws  |
| dog     | Buffy  | Fang   |
| dog     | Buffy  | Bowser |
+---------+--------+--------+

Using an INNER JOIN (instead of a LEFT JOIN). Also defined the Female pets as alias "p1":

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1
INNER JOIN pet AS p2 ON p1.species = p2.species
WHERE p1.sex = 'f' AND p2.sex = 'm';

+---------+--------+--------+
| Species | Female | Male   |
+---------+--------+--------+
| cat     | Fluffy | Claws  |
| dog     | Buffy  | Fang   |
| dog     | Buffy  | Bowser |
+---------+--------+--------+

Using the dead pet filter:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species
AND p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL;

+---------+--------+-------+
| Species | Female | Male  |
+---------+--------+-------+
| cat     | Fluffy | Claws |
| dog     | Buffy  | Fang  |
+---------+--------+-------+

The above query with the dead filter but using an INNER JOIN:

SELECT p1.species AS Species, p1.name AS Female, p2.name AS Male
FROM pet AS p1
INNER JOIN pet AS p2 ON p2.species = p1.species
WHERE p1.sex = 'f' AND p2.sex = 'm'
AND p1.death IS NULL AND p2.death IS NULL;

+---------+--------+-------+
| Species | Female | Male  |
+---------+--------+-------+
| cat     | Fluffy | Claws |
| dog     | Buffy  | Fang  |
+---------+--------+-------+

Posted by Robert Ameeti on February 16 2007 6:44am

[Delete] [Edit]

The text of the tutorial says:

"The query uses WHERE clause to match up records in the two tables based on the name values."

But in fact the WHERE clause does nothing to match up records but rather to specify which event type to select. It is the FROM or the ON that specifies which records to match up.

Posted by Keith Corlett on February 19 2007 1:51pm

[Delete] [Edit]

Bob makes an interesting point... and I think it's probably worth descending into the difference between the ON clause and the WHERE clause at this juncture... as it doth tend to cause newbies grief.

So the lonely pets club query...

select p.name
from pet p left join event e
on e.name = p.name
where e.name is null;

+----------+
| name     |
+----------+
| Puffball |
+----------+

finds all the pets which do not have a correlated event.

This is a useful query "pattern" for locating non-nullable foreign key values which have broken the foreign key constraint... and it's fast.

But what about this one?

select p.name
from pet p left join event e
on e.name = p.name
and e.name is null;

+----------+
| name     |
+----------+
| Fluffy   |
| Claws    |
| Buffy    |
| Fang     |
| Bowser   |
| Chirpy   |
| Whistler |
| Slim     |
| Puffball |
+----------+

This query is a truism... it's just a complicated way of returning all pets names. It's a common mistake.

Add your own comment.

Top / Previous / Next / Up / Table of Contents