Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!skynet.be!skynet.be!213.51.129.3.MISMATCH!newshub1.home.nl!home.nl!news2.euro.net!postnews1.euro.net!news.wanadoo.nl!not-for-mail
From: Jaap W. van Dijk <j.w.vandijk.removethis@hetnet.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: tricky SQL SELECT - help!
Date: Sun, 02 Feb 2003 22:55:47 +0100
Message-ID: <vs3r3vkf0gpc0glhgbfbrr4t6f3nttjhkc@4ax.com>
References: <9b08c59d.0301302221.6c859855@posting.google.com>
X-Newsreader: Forte Free Agent 1.92/32.572
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 45
Organization: Wanadoo
NNTP-Posting-Date: 02 Feb 2003 21:52:42 GMT
NNTP-Posting-Host: rot2-p3281.dial.wanadoo.nl
X-Trace: 1044222762 news.wanadoo.nl 23918 62.234.207.209:1360
X-Complaints-To: abuse@wanadoo.nl
Xref: newsfeed1.easynews.com comp.databases.oracle.server:174527
X-Received-Date: Sun, 02 Feb 2003 14:52:00 MST (news.easynews.com)

On 30 Jan 2003 22:21:44 -0800, kittycatangel@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.