Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

t-sql look for records in two tables

At this link
http://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table

I saw this example which selects all records from one table that do not exist in another table.
So it looks for the records in table t1 that do not exist in table t2

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL


What's the syntax to look for records in two tables?
So look for the records in table t1 that do not exist in table t2 or table t3 ?

Anyone know?
0
maqskywalker
Asked:
maqskywalker
1 Solution
 
PaulCommented:
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.name = t2.name
LEFT JOIN table3 t3 ON t1.name = t3.name
WHERE t2.name IS NULL or t3.name IS NULL

Open in new window

This is sometimes referred to as a "Left Excluding JOIN"
see: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Note if you only want rows in T1 that have no match in either t2 and t3 then change the OR to AND

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.name = t2.name
LEFT JOIN table3 t3 ON t1.name = t3.name
WHERE t2.name IS NULL    and       t3.name IS NULL

Open in new window

1
 
maqskywalkerAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now