Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

t-sql look for records in two tables

Posted on 2016-11-07
2
Medium Priority
?
83 Views
Last Modified: 2016-11-07
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
Comment
Question by:maqskywalker
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41877987
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
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41877993
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question