Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tricky SQL SELECT - 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)
BTW, what vendor dreamed up this abortion? Jim
-- Replace part of the email address: kennedy-down_with_spammers_at_attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "Angel Cat" <kittycatangel_at_hotmail.com> wrote in message news:9b08c59d.0301302221.6c859855_at_posting.google.com...Received on Fri Jan 31 2003 - 11:03:59 CST
> 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!