Correct inner join

• Krasavita

  SSC-Insane

  Points: 23138

  More actions

  May 10, 2011 at 9:35 am

  #389492

  table 1 and table 2

  table1

  ID MACId

  5 1

  5 2

  5 3

  5 4

  table2

  ID MACId

  5 1

  5 2

  5 4

  How can I write select statment

  I want to select all records from table 1 inner joint by Id,but I don't want records if they have the same MACID, so my result should be 5 3

  This is what I did:

  SELECT dbo.Table_1.macid

  FROM dbo.Table_1 INNER JOIN

  dbo.Table_2 ON dbo.Table_1.id = dbo.Table_2.id

  WHERE (1 = 1) AND (dbo.Table_1.macid NOT IN

  (SELECT MacId

  FROM dbo.Table_2 AS Table_2_1))

  I sthis correct?

  Thank you

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  May 10, 2011 at 10:17 am

  #1322453

  Not quite, you are joining on on one part and filtering with the other

  Try this

  SELECT a.[ID],a.MACId

  FROM dbo.Table_1 a

  WHERE NOT EXISTS (SELECT * FROM dbo.Table_2 b WHERE b.[ID]=a.[ID] AND b.MACId=a.MACId)

  Far away is close at hand in the images of elsewhere.
  Anon.

• Krasavita

  SSC-Insane

  Points: 23138

  More actions

  May 10, 2011 at 10:26 am

  #1322469

  thank you