November 9, 2002 at 12:23 pm
What t-sql statement(s) would you use to accomplish this?
Input Table1
+-----------+-----------+
| first | last |
------------+-----------+
| Peter | Smith|
' Ted | Smith |
| John | Johnson |
| Joe | Townsend |
| Jerry | Mangona |
| Mike | Gallagher|
| Mike | Lowry |
------------+-----------+
Input Table2
+-----------+-----------+
| first | last |
------------+-----------+
| Peter | Smith|
| John | Ricardo |
| Joe | Townsend |
| Jerry | Tuna |
| Mike | Lowry |
------------+-----------+
Output table consists of those rows in first table that do not appear in second table:
OutputTable
+-----------+-----------+
| first | last |
------------+-----------+
| Ted | Smith|
| John | Johnson |
| Jerry | Mangona |
| Mike | Gallagher |
------------+-----------+
Why in Output table?
(Ted, Smith) in Table 1, but no matching record in Table2. Closest match in Table2 is (Peter, Smith) but first name fields differ so regarded as a new record
(John, Johnson) -- differs in last name field
(Jerry, Mangona), (Mike, Gallagher) --not found in Table2 but present in Table1
TIA,
Bill
November 9, 2002 at 12:38 pm
SELECT first, last
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2
WHERE table2.first = table1.first and
table2.last = table1.last )
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 9, 2002 at 5:36 pm
Or try this:
SELECT T1.first, T1.last
FROM
table1 T1
LEFT JOIN
table2 T2
ON
T1.First = T2.First AND
T1.Last = T2.Last
WHERE
T2.First IS NULL
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply