SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL query - finding non-matching data in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnsley
Starting Member

31 Posts

Posted - 06/22/2011 :  06:14:02  Show Profile  Reply with Quote
I have 2 tables and would like to display only the non-matching data in 1 of the tables which doesn't appear in the 2nd table.

e.g.
Table1    Table2
Rod         Rod
Jane        Jane
Freddy     Sam
xxx

So my query should find 1 record - 'Sam'.

I can display matching records, but not the ones which don't match.
Table1 is much larger than the other - and it displays every record in Table1 and multiple entries from Table2 (becasue they aren't equal at some point).

Code for displaying matching records:
SELECT     TOP 100 PERCENT Table1.Name AS Tbl1Name, Table2.Name AS Tbl2Name
FROM         Table2 INNER JOIN
                      Table1 ON Table2.Name = Table1.Name


(to display non-matching records - I tried using <> instead of = )
thanks in advance.

mark.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/22/2011 :  06:58:40  Show Profile  Reply with Quote
If you are looking for rows in Table1 that are not in Table2, you can do this:
SELECT
	Table1.Name
FROM
	Table1
WHERE
	NOT EXISTS
	(
		SELECT * FROM Table2
		WHERE Table2.Name = Table1.Name
	)
If you want to find names that are in one table but not in the other, you can use a full join.

SELECT
	Table1.Name AS Tbl1Name,
	Table2.Name AS Tbl2Name
FROM
	Table2
	FULL JOIN Table1
		ON  Table2.Name = Table1.Name
WHERE
	Table1.Name IS NULL
	OR Table2.Name IS NULL;
In a similar way, the first query could be rewritten to use a left join.
Go to Top of Page

barnsley
Starting Member

31 Posts

Posted - 06/22/2011 :  08:13:13  Show Profile  Reply with Quote
thanks sunitabeck,
that works!

mark.
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 06/23/2011 :  00:31:19  Show Profile  Visit jcelko's Homepage  Reply with Quote
Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Basic set operators from junior high school?

(TableA
UNION
TableB) AS Everything
EXCEPT
(TableA
INTERSECT
TableB) AS CommonStuff



--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000