Dear list,
I've done basic SQL for a number of years, but I recently came up
against a query I can't quite seem to figure out the join syntax for.
Suppose I have rows in an Object table which are linked in a
hierarchy by the entries in a Link table and I want to find all
Objects which have given set of Objects as children.
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?
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).
TIA,
--
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
