Daenmark

October 26, 2006

SQL for checking whether a set already exists

The following SQL will give all other lists which exactly match the specified list. Works because, in the case of equality, the union and intersection of two sets is the same, so the difference between them (the 'MINUS' operator in Oracle) returns the null (empty) set, which is tested for by 'NOT EXISTS'. Nifty, eh?

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 
    UNION 
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = lt.list_id) 
      MINUS
  (SELECT list_id 
  FROM   list_table 
  WHERE  list_id = :list_id 
    INTERSECT 
  SELECT list_id 
  FROM   list_table 
  WHERE  list_id = lt.list_id) 
 )
AND 
  lt.list_id <> :list_id;

Posted by daen at 02:14 AM | Comments (0)

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 02:14 AM | Comments (0)