Using 2 fields to check one table against another

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

SQL Server 2000 Forums

SQL Server Development (2000)

Using 2 fields to check one table against another

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2006-03-27 : 17:05:49

I have two tables that I need to compare to find the missing records. There are 2 fields combined that make the records unique.

Table1 SLDOCO,SLLITM
Table2 ILDOCO,ILLITM

I need to check for records in Table1 that are not in Table2 based on these 2 fields in each

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-27 : 19:09:00

You can start with:

SELECT a.SLDOCO, a.SLLITIM, b.ILDOCO, b.ILLITM
   FROM Table1 AS a
   FULL JOIN Table2 AS b
      ON (b.ILDOCO = a.SLDOCO
      AND b.ILLITM = a.SLLITM)

-PatP

misterzr
Starting Member

49 Posts

Posted - 2006-03-27 : 23:21:27

Correct me if I'm wrong but wouldn't this give me the common records from each, I am looking for the records in Table1 that don't exist in Table 2 based on the matching of the 2 fields

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 01:07:56

Select * from table1 t1 where not exists
(select * from table2 where col1=t1.col1 and col2=t1.col2)

Madhivanan

Failing to plan is Planning to fail

misterzr
Starting Member

49 Posts

Posted - 2006-03-28 : 09:54:12

[code]SELECT SDLITM, SDDOCO, SDDCTO, SDDOC, SDDRQJ
FROM PRODDTA.F42119 WHERE NOT EXISTS
(SELECT * FROM PRODDTA.F4111
WHERE ILDOCO = PRODDTA.F42119.SDDOCO
AND ILLITM = PRODDTA.F42119.SDLITM
)
AND SDDCTO ='SF'
AND SDDRQJ >'106001'
AND SDDOC <> '0'
ORDER BY SDDOCO,SDLITM[/code]

Thanks, this works great

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-28 : 14:22:52

No, this is where the FULL JOIN really shines. Try this:

SELECT CASE
      WHEN b.ILDOCO IS NULL THEN 'A Only'
      WHEN a.SLDOCO IS NULL THEN 'B Only'
      ELSE 'In both'
   END
,  a.SLDOCO, a.SLLITIM, b.ILDOCO, b.ILLITM
   FROM Table1 AS a
   FULL JOIN Table2 AS b
      ON (b.ILDOCO = a.SLDOCO
      AND b.ILLITM = a.SLLITM)

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources