CODEWITHSUNDEEP
sqlsql-serverjoindistincttemp-tables
VQB
VQB

Reputation: 79

SQL: JOIN problem using temp tables and one column

I am created two temp tables in which TABLE1 contains all the items and TABLE2 only has the partial list of TABLE1. How can I find out which parts TABLE 1 has that TABLE2 doesn't have or vice versa? Please keep in mind, the temp table only has one column due to the DISTINCT statement.

I do have to use Joins but my thought is if I JOIN on the individual columns of each table and then in the Where clause state that e.g. column 1 is not equal column 2, it's contradicting. 

IF EXISTS   (
            SELECT *
            FROM tempdb.dbo.sysobjects
            WHERE id = Object_id(N'tempdb..#TABLE1')
            )
        BEGIN
            DROP TABLE #TABLE1
        END

IF EXISTS   (
            SELECT *
            FROM tempdb.dbo.sysobjects
            WHERE id = Object_id(N'tempdb..#TABLE2')
            )
        BEGIN
            DROP TABLE #TABLE2
        END
------------------------------------------------
select distinct 1.parts as #TABLE1 from List1 1  --- MAIN LIST

select distinct 2.parts as #TABLE2 from List2 2  --- ADDITIONAL LIST

select *
from #TABLE2 left join
     #TABLE1
     on 2.parts = 1.parts
where 2.parts <> 1.parts

Upvotes: 0

Views: 898

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your where clause is undoing the left join. I would recommend not exists:

select t1.*
from #table1 t1
where not exists (select 1 from #table2 t2 where t2.parts = t1.parts);

Upvotes: 1

Related Questions