Not In / Left Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Not In / Left Join

Author

Topic

Danny Z
Starting Member

1 Post

Posted - 2011-06-09 : 12:31:40

How does one go about locating records in one table not present in another table where multiple columns are used to denote a unique row?

For example:
tblA: PROC_CODE, MOD_1, MOD_2
tblB: PROC_CODE, MOD_1, MOD_2

The three columns in each table provide uniqueness to each row, but (here is the issue) the data in MOD_1 and MOD_2 may contain null values.

Example of data in tblA:

PROC_CODE MOD_1 MOD_2
S5000
S5000 AA
S5000 AA AB

Example of data in tblB:

PROC_CODE MOD_1 MOD_2
S5000
S5000 AA AB

If I want to find the records in tblA that do not exist in tblB, the results should be:

PROC_CODE MOD_1 MOD_2
S5000 AA

Thanks in advance!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-09 : 13:07:42

Here's an option:

-- set up
CREATE TABLE #tblA(PROC_CODE CHAR(5) NOT NULL, MOD_1 CHAR(2) NULL, MOD_2 CHAR(2) NULL)
CREATE TABLE #tblB(PROC_CODE CHAR(5) NOT NULL, MOD_1 CHAR(2) NULL, MOD_2 CHAR(2) NULL)

INSERT #tblA 
VALUES('S5000',NULL,NULL)
,('S5000','AA',NULL)
,('S5000','AA','AB')

INSERT #tblB
VALUES('S5000',NULL,NULL)
,('S5000','AA','AB')

-- SQL Server 2005+
SELECT * FROM #tblA EXCEPT SELECT * FROM #tblB 

-- MS Access, SQL Server
SELECT PROC_CODE,MOD_1,MOD_2 FROM (
SELECT 'A' tbl,PROC_CODE,MOD_1,MOD_2 FROM #tblA 
UNION ALL
SELECT 'B' tbl,PROC_CODE,MOD_1,MOD_2 FROM #tblB) z
GROUP BY PROC_CODE,MOD_1,MOD_2
HAVING MAX(tbl)='A'

You can ignore the setup part and substitute the proper table names in the other query(ies).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources