Multi-part key restriction query

• John Hanrahan

  Hall of Fame

  Points: 3825

  More actions

  February 2, 2011 at 8:39 am

  #233045

  I have an issue with restricting records with multi-part keys.

  Table A (Key1 INT, Key2 INT, Data...)

  Table B (Key1 INT, Key2 INT, DATA...)

  So I want to write a query that is

  SELECT everything from table A except where it's in table B

  I can think of quite a few logical ways to do this but have not found the proper syntax. I always end up doing some work around but I think there has to be a way.

  Any suggestions? Thanks in advance.

  John

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  February 2, 2011 at 11:26 am

  #1281582

  Sorry if this is oversimplifying your query, but do you just mean:

  SELECT * FROM Table1 EXCEPT (SELECT * FROM Table2) ?

  Example:

  CREATE TABLE #A(

  a int,

  b int)

  INSERT INTO #A SELECT 5,1

  INSERT INTO #A SELECT 6,1

  INSERT INTO #A SELECT 7,1

  CREATE TABLE #B(

  a int,

  b int)

  INSERT INTO #B SELECT 4,1

  INSERT INTO #B SELECT 6,1

  INSERT INTO #B SELECT 7,1

  SELECT * FROM #A EXCEPT (SELECT * FROM #B)

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• John Hanrahan

  Hall of Fame

  Points: 3825

  More actions

  February 2, 2011 at 11:42 am

  #1281584

  That does what I'm looking for.

  I wonder how the performance is on something like that?

  Thank you