Re: tricky SQL SELECT - help!

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
>
> 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
>
> So, John has 1 cat, no dogs. In SQL statement, how can I retrieve what
> pets John has? Subqueries doesn't seem to allow the 'like' clause..
>
> I need a SELECT like this:
>
> SELECT ID FROM PEOPLE WHERE NAME = "JOHN" AND ID LIKE "%(SELECT
> PEOPLE_IDS FROM PETS' %"
>
> But the syntax doesn't work like that. :confused: I also need this
> done in 1 statement, no DECLARE variables in 2 lines
>
> Any help is appreciated!

You don't need a select statement ... what you need is DROP TABLE.

This is not just not normalized it is nightmarized.

What you are trying will at best be difficult, slow, unscalable, and miserable. Unless you are paid by the hour with unlimited overtime you should parse this mess once and normalize the tables to something like this:

PEOPLE
ID NAME
1 John

PETS
PeopleID Pet_Type

1                    C

There isn't enough scotch in Scotland to make me want to work with what you have.

Daniel Morgan Received on Fri Jan 31 2003 - 01:38:05 CST