Re: tricky SQL help!

TurkBear wrote:

> Try this way, but a redesign will be better:
> You want the pets John has so the select is from the pets table with the where clause using the people table:
>
> select pet from pets where people_id like (select '%'||to_char(id)||'%' from people where name = 'John')
> This one creates a %ID% string to use with the like operator
>
> You want all people who have Dogs:
>
> select name from people where id IN (select '('||replace(people_ids,';',',')||')' where pettype = 'Dog');
> This example creates a comma-separated list from your semi-colon one, so the IN operator can work...
>
> Untested, so try it yourself...
>
> hth,
>
> 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
> >
> >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!

Good try but it doesn't work.

One person id is 1, another 10, another 11, another 21, another 111. Any concatenation with LIKE will find them all.

Daniel Morgan Received on Fri Jan 31 2003 - 17:30:48 CST