Re: tricky SQL SELECT - help!

On 30 Jan 2003 22:21:44 -0800, kittycatangel_at_hotmail.com (Angel Cat) wrote:

...
>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' %"
>

...
Aside from other problems you are going to have with this interesting model:

SELECT ID FROM PEOPLE WHERE NAME = "JOHN" AND exists (select null from pets where
instr(people_ids||';',id||';')> 0)

The semicolon concatenated to people_ids is thrown in to take care that every id in the resulting string is terminated by a semicolon.

A OVERALL JOIN would look like:

select *
from people, pets
where 0 < instr(people_id||';',id||';')

A DELETE of a person

update pets
set people_ids = replace(people_ids||';', ';'||&id||':');

delete people
where id = &id;

A DELETE of a PEOPLE-PET relation occurrence:

update pets
set people_ids = replace(people_ids||';', ';'||&id||':') where pettype = &pettype;

Ensoforth.

Jaap. Received on Sun Feb 02 2003 - 15:55:47 CST