CODEWITHSUNDEEP
sql-server
Daniel A Sathish Kumar
Daniel A Sathish Kumar

Reputation: 157

Which join i want to use to get rows from left table that not found in right table in SQL Server

I have two tables has below

Table Left:

ID   | Visit No 
---  | --------
 1   |   1
 1   |   2
 2   |   1
 3   |   1

If Table Right has

ID  | Visit No | Place
--- | -------- | -----
 1  |  1       | Chennai

Then Results will be like 

 ID  | Visit No 
---  | --------     
 1   |   2
 2   |   1
 3   |   1

If Table Right is Empty, Then the Result will be

ID   | Visit No 
---  | --------
 1   |   1
 1   |   2
 2   |   1
 3   |   1

Based on ID and Visit No. Is it possible to get output using joins in SQL Sever 2012.

Upvotes: 0

Views: 39

Answers (1)

Geert Immerzeel
Geert Immerzeel

Reputation: 564

SELECT left.[ID], left.[Visit No]
FROM LeftTable left
LEFT JOIN RightTable right
  ON left.[ID] = right.[ID]
  AND left.[Visit No] = right.[Visit No]
WHERE right.[ID] IS NULL

Upvotes: 1

Related Questions