Re: Tricky Join with Animal-DB

[Roger Baklund]

Martin Rytz wrote:
[...]
With which join can I show the animals with all pics for this animal (even
if there are more than one pic for an animal)?
SELECT animal,pic
  FROM animal_pic,animal,pic
  WHERE
    animal.id = animalid AND
    pic.id = picid
This will list all animals which have pictures, with all their pictures. 
If you wanted a special animal, you could just add "AND animal = 'Turtle'".

The above "shorthand" way of writing a join is the same as the following 
more explicit join, note that the WHERE clause is used only for result 
conditions, the join conditions are moved to the ON parts of the joins:

SELECT animal.animal,pic.pic
  FROM animal_pic
  INNER JOIN animal ON
    animal.id = animal_pic.animalid
  INNER JOIN pic ON
    pic.id = animal_pic.picid
  WHERE
    animal = 'Turtle'
If you also wanted animals _without_ pictures in the list, you could use 
a LEFT JOIN:

SELECT animal.animal,pic.pic
  FROM animal_pic
  INNER JOIN animal ON
    animal.id = animal_pic.animalid
  LEFT JOIN pic ON
    pic.id = animal_pic.picid
Finally, if you also wanted pictures without animals, you could LEFT 
JOIN both tables:

SELECT animal.animal,pic.pic
  FROM animal_pic
  LEFT JOIN animal ON
    animal.id = animal_pic.animalid
  LEFT JOIN pic ON
    pic.id = animal_pic.picid
<URL: http://dev.mysql.com/doc/mysql/en/join.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]