CODEWITHSUNDEEP
sql-server
user2588088
user2588088

Reputation: 47

How to join 2 tables using SQL

I created two temporary tables, #tableA (1872 entries) and #tableB (1146 entries). I have populated both with mailing info. I want to filter out the entries from #tableA that also show up in #tableB (437 shared entries). I want to do this by looking at the FullName field found in both temp tables. After we filter these shared names out I need to return the data from #tableA (which now has 1435 entries).

select * from #TableA
--inner join #TableB ON #TableA.FullName = #TableB.FullName --shows 437 shared names
where 
--#TableA.FullName <> #TableB.FullName --this is obviusly not going to work

Could you guys please point me in the right direction?

thank you

Upvotes: 0

Views: 83

Answers (4)

Virat Singh
Virat Singh

Reputation: 61

This query will give you all the records from TableA and any matching records from TableB. Records that are not present in TableB will be NULL.

SELECT
  a.*,
  b.*
FROM
  #TableA a
  LEFT OUTER JOIN #TableB b
    ON a.FullName = b.FullName

Upvotes: 0

Andrew
Andrew

Reputation: 7768

I rather advise you to learn joins. If you don't know it- you need to! Otherwise you will be asking others to do it for you.

enter image description here

Upvotes: 3

user38858
user38858

Reputation: 316

Maybe try this? It creates a CTE containing only entries where they match, then filters those out in the select statement.

;with aggTable as(
select ta.FullName
from #tableA ta
inner join #tableB tb
on ta.FullName = tb.FullName
)
select *
from #tableA ta
where ta.FullName not in aggTable

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

select a.*
from #TableA a
left join #TableB b ON a.FullName = b.FullName
where b.FullName IS NULL

Upvotes: 5

Related Questions