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 October 26, 2006 02:14 AM
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.)