Re: tricky SQL SELECT - help!

On 30 Jan 2003 22:21:44 -0800, kittycatangel_at_hotmail.com (Angel Cat) wrote:

>Hello!
>
>I have 2 tables that I need to join together in a SELECT but it
>doesn't seem easy
>
>PEOPLE table
>ID int
>NAME varchar

Nothing wrong here.

>PETS table
>PETTYPE varchar
>PEOPLE_IDS varchar

Something wrong here.

>The PEOPLE table links to the PETS with the PEOPLE's IDs separated by
>semicolons!

This is definetly wrong (in my opinion at least). Doing this you violate the principles that Oracle and mucht other DBMS'es are based upon.

I agree with Daniel (above), and I seriously think you are not up to this kind of stuff (yet), but miracles exist, maybe I can help a little more to get you started.

What you need has to do with normalization and data-modelling (huh?). In fact the PEOPLE_IDS in PETS are pointers to PEOPLE and it is inconvenient to put more than one pointer in one field. So in this case I think you should not combine PEOPLE_IDS in one record of PETS. The proper way to do it is to create multiple records in PETS for all combinations of PET and PEOPLE.

PETS looks like this:

>PETS table
>PETTYPE varchar
>PEOPLE_ID int (identical to ID from PEOPLE table.

So, PETS will have records like this
PETTYPE PEOPLE_ID
Cat 1
Cat 34
Cat 35
Cat 88
Cat 101
Cat 122
Dog 4
Dog 33
Dog 34

SELECT PETS.PETTYPE from PETS
, PEOPLE
WHERE PETS.PEOPLE_ID = PEOPLE.ID
AND PEOPLE.NAME = 'John';

This really is the way to do it. There is FAR more to it, but i guess this is enough for now.

REALLY STRONG advice: Read a book about SQL and then about data modelling or normalization, you really need this. Otherwise you better use a Word processor or Spreadsheet program for your administration.

Good luck, you'll need it.

Hans van Dam. Received on Fri Jan 31 2003 - 02:35:56 CST