Solved

t-sql look for records in two tables

Posted on 2016-11-07
2
20 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 48

Accepted Solution

by:
PortletPaul earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now