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; > 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; Hope this simple queries solves your problem. -- dsoares (sql) --------------------------------------------------------------------- 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
