« My favourite lightbulb joke | Main | Dragsholm Slot »

October 26, 2006

SQL for checking whether a set exists as a subset

The following SQL will give all other lists which contain the specified list as a strict subset. Works for Oracle, haven't tried it for MySQL or SQLServer ... YMMV.

SELECT   lt.list_id
FROM     list_table lt  
GROUP BY lt.list_id 
HAVING NOT EXISTS (
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = :list_id 
    MINUS 
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = lt.list_id) 
AND 
  lt.list_id <> :list_id;

Posted by daen at October 26, 2006 02:14 AM

Comments

Post a comment

Thanks for signing in, . Now you can comment. (sign out)

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)


Remember me?