Re: tricky SQL SELECT - help!

Ok, you have a problem with your database structure here. You can't write the SQL you want because your database is not normalized. What you need is a table for people, a table for pets, and a table to link the two together. There is a many to many relationship between people and pet types, which is difficult to implement with only two tables, as you have discovered. If you can, rearrange your database like this:

PEOPLE table
ID int
NAME varchar

eg
ID NAME
1 John

PETS table
ID int
PETTYPE varchar

eg
ID TYPE
1 Cat
2 Dog

PEOPLE_PETS table
PEOPLE_ID int
PET_ID int

eg

PEOPLE_ID     PET_ID
1                        1
3                        1

So you list people in one table, pets in another, and who owns what in a third. This means that if anyone has more than one pet, they have more than one entry in the link table.

So John has one entry for a cat, but no entry for a dog.

You can then list what pets John has with SELECT PETTYPE
FROM PETS
JOIN PEOPLE_PETS ON (PETS.ID = PEOPLE_PETS.PET_ID) JOIN PEOPLE ON (PEOPLE.ID = PEOPLE_PETS.PEOPLE_ID) WHERE PEOPLE.NAME = 'John'; Received on Fri Jan 31 2003 - 03:23:59 CST