Re: tricky SQL help!

kittycatangel_at_hotmail.com (Angel Cat) wrote in message news:<9b08c59d.0301302224.233b4672_at_posting.google.com>...
> 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
>
> PETS table
> PETTYPE varchar
> PEOPLE_IDS varchar
>
> The PEOPLE table links to the PETS with the PEOPLE's IDs separated by
> semicolons!
>
> So, PETS will have records like this
> PET PEOPLE_IDS
> Cat 1;34;35;88;101;122
> Dog 4;33;34;
>
> And PEOPLE will have records like this:
> ID NAME
> 1 John

Is there any chance you can alter the table structure to something like:

PEOPLE table       PETS table          PETS_PEOPLE
------------       ---------------     -----------
ID   int           PETTYPE varchar     PETTYPE varchar
Name varchar                           ID      int

So the tables

PEOPLE           PETS
1   John         Cat  1;34;35;88;101;122
4   Dave         Dog  4;33;34;

Would become

PEOPLE           PETS        PETS_PEOPLE
1   John         Cat         Cat    1
4   Dave         Dog         Cat   34
                             Cat   35
                             Cat   88
                             Cat  101
                             Cat  122
                             Dog    4
                             Dog   33
                             Dog   34

You may want to change some of the primary keys, etc. But this type of design ought to make most questions involving both tables a lot easer.

To see what John has:

Select pettypes
from pets_people pp, people p
where pp.id = p.id and name = 'John'

Jim Lyons
University of Texas at Austin Received on Fri Jan 31 2003 - 14:49:13 CST