I figured it out ...
At 3:22 PM +0100 5/20/02, ds wrote:
>On Mon, 2002-05-20 at 13:34, Ray Zimmerman wrote:
>...
> > For example, given the following data ...
>>
>> CREATE TABLE Object (
>> id int(11) NOT NULL auto_increment,
>> PRIMARY KEY (id)
>> );
>>
>> INSERT INTO Object (id) VALUES (1);
>> INSERT INTO Object (id) VALUES (2);
>> INSERT INTO Object (id) VALUES (3);
>> INSERT INTO Object (id) VALUES (4);
>> INSERT INTO Object (id) VALUES (5);
>> INSERT INTO Object (id) VALUES (6);
>>
>> CREATE TABLE Link (
>> parent int(11) NOT NULL,
>> child int(11) NOT NULL,
>> );
>>
>> INSERT INTO Link (parent, child) VALUES (1, 4);
>> INSERT INTO Link (parent, child) VALUES (1, 5);
>> INSERT INTO Link (parent, child) VALUES (2, 4);
>> INSERT INTO Link (parent, child) VALUES (2, 5);
>> INSERT INTO Link (parent, child) VALUES (2, 6);
>> INSERT INTO Link (parent, child) VALUES (3, 4);
>>
>> ... I want to find all Objects that have exactly 2 children with ids
>> 4 and 5 (i.e. should match 1, but not 2 or 3) ... what's the query
>> syntax?
>
>SELECT parent FROM Link WHERE child=4 OR child=5
>GROUP BY child HAVING count(*)=2;
Actually, while this may work for the specific example, it doesn't
work in general ... here's the query I finally found to work ...
SELECT * FROM
Object LEFT OUTER JOIN Link L0 ON
(Object.id = L0.parent AND L0.child NOT IN (4,5)),
Link L1,
Link L2
WHERE L1.parent = Object.id AND L1.child = 4
AND L2.parent = Object.id AND L2.child = 5
AND L0.id IS NULL;
> > How about if I want to find all Objects which have no children (i.e.
>> should match 4, 5 and 6, but not 1, 2 or 3).
>
>SELECT id FROM Object LEFT JOIN Link ON (Object.id=Link.parent)
>WHERE Link.parent IS NULL;
And yes, this is basically what I came up with here too. Thanks.
--
Ray Zimmerman / e-mail: [EMAIL PROTECTED] / 428-B Phillips Hall
Sr Research / phone: (607) 255-9645 / Cornell University
Associate / FAX: (815) 377-3932 / Ithaca, NY 14853
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
