Re: tricky SQL help!

Jim Lyons wrote:

> 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

He claims he can't and he shows no willingness to go to management etc. etc. etc. which makes me think this entire thread is made up by a student in an attempt to cover for something he's done in class and doesn't want to undo.

But the best solution I've come up with given the OP's unwillingness to do the right thing ... is to create a normalized table and load it with a stored procedure a couple of times a day.

Daniel Morgan Received on Fri Jan 31 2003 - 17:33:37 CST