Re: tricky SQL help!

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!
Received on Fri Jan 31 2003 - 12:32:50 CST