CODEWITHSUNDEEP
sql-servert-sql
Vaso Miruashvili
Vaso Miruashvili

Reputation: 109

Join unique values SQL Server

I have data in a #temp table like this: 

ID              Connection  
---------------------------
ID100931969     C170893167  
ID451076784     C170893167

I want to join same structured data in Tempo like this, on Connection:

ID          Connection  
----------------------
ID100931969 C170893167  
ID451123343 C170893167

Here is the code I wrote:

select  
    t.ID,
    t.Connection,
    t.Detail_ID 
from 
    #temp t
join 
    Tempo as s on s.Connection = t.Connection   
where 
    t.ID <> s.ID

Problem here is that I don't want to join same data in both tables, so if t.ID=s.ID and t.Connection=s.Connection I don't want to join this data, in this example:

ID100931969 and C170893167.

Hope I explained clearly.

Upvotes: 0

Views: 42

Answers (2)

Mukesh Arora
Mukesh Arora

Reputation: 1813

You can use the below query if you want to fetch all the records from #temp which does not exist in the tempo

Note: This will work if both the table have the same structure (columns)

select * from #temp  
EXCEPT  
select * from tempo

Upvotes: 0

PSK
PSK

Reputation: 17943

You can change your condition like following.

select  t.ID,
 t.Connection,
 t.Detail_ID 
from #temp t
 join Tempo as s on s.Connection<> t.Connection and    t.ID<>s.ID

This will join all the records which are not matching based on connection and id

Looking at your scenario, it seems you want record from #temp table which are not matching in Tempo, for this you can use NOT EXISTS like following.

select  t.ID,
 t.Connection,
 t.Detail_ID 
from #temp t
 where not exists
 (
  select 1 from Tempo t1 where t1.id=t.id and t.connection=t1.connection
 )

Upvotes: 1

Related Questions