Re: tricky SQL help!

I've see this type of thing before. If you can fix the data model. This is not the way to model this data. It is a popular technique with programmers who are not seasoned in using databases.

You need to create a function that will parse through a row and see if the element is in the row. eg
create or replace function is_an_element(canidate in varchar2, row_in in varchar2) return number as
-- compare the two and determine if it is in the canidate is in the row and return 1 if so else 0
end;
/

select id from people p where
NAME = "JOHN" AND exists(select 1 from pets pt where (p.id,pt.people_ids)=1)

Jim
"Angel Cat" <kittycatangel_at_hotmail.com> 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
>
> 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 - 01:10:19 CST