SQL Question (SQLServer2K specific)

McGintyMcGoo

I want to subtract the values of one query from another query. I know that the SQL standard has a command called "EXCEPT", but I do not think the MSSQL2K supports this statement. Do you guys have any other suggestions?

pseudo sql ....
SELECT * FROM TABLE WHERE CRITERIA
EXCEPT (SELECT * FROM TABLE WHERE CRITERIA)

Cheers

McGintyMcGoo

Just found an article on Books Online about
"Using EXISTS and NOT EXISTS to Find Intersection and Difference"

Should have it solved soon!

bonkey

Here's how I'd do it :

SELECT   <select-field-list>
  FROM   table1 t_all
  LEFT OUTER JOIN 
         table2 t_exclude
    ON   t_all.primarykey = t_exclude.primarykey
  WHERE  t_exclude.primarykey IS NULL

Obviously you need to rename fields appropriately.

This method will work with compsite primary keys - you just end up with one entry per field in the ON clause for the join statment, and one entry per field in the WHERE.

Note : if table1 and table2 are the same table, this still works, as long as you remember to alias the tablenames, as has been done in the example.

jc

bonkey

Originally posted by McGintyMcGoo
Just found an article on Books Online about
"Using EXISTS and NOT EXISTS to Find Intersection and Difference"

Should have it solved soon!

D'oh. EXISTS is the easy way of doing it, yeah

I just tend to not use it...I have a sneaking suspicion it is less efficient, but maybe thats just me.

jc

McGintyMcGoo

Bonkey

Thanks for your help! That worked a treat!!!!!!!!!