CODEWITHSUNDEEP
sqlsql-serverself-join
user1429135
user1429135

Reputation: 155

Need help in Self Join

I've a Table XYZ:

ID   A       B       C
1    abc     ygh     NULL
2    fgfd    bjh     NULL
3    jhkj    fgd     cgbvc
1    NULL    NULL    yr
2    NULL    NULL    jg

I need to do a self join on the above table to get only the matching IDs. I'm trying to use below query:

Select T1.ID, T1.A, T1.B, T2.C
From XYZ T1
INNER JOIN XYZ T2
where T1.ID = T2.ID

But not getting below result:

1   abc     ygh     yr
2   fgfd    bjh     jg

Please advise. Krishna

Upvotes: 2

Views: 81

Answers (4)

Mysterious Otter
Mysterious Otter

Reputation: 4029

Given this table structure, you could also use COALESCE: 

SELECT 
    COALESCE(a.A, b.A) A,
    COALESCE(a.B, b.B) B,
    COALESCE(a.C, b.C) C
FROM 
    XYZ a 
JOIN 
    XYZ b ON a.Id = b.Id
WHERE
    a.A IS NULL OR a.B IS NULL OR a.C IS NULL

Upvotes: 0

kaushik0033
kaushik0033

Reputation: 677

You can use below query but its based on WHERE clause.

select ID,A,B,C from (
select *, ROW_NUMBER() over (partition by id order by s desc ) as 'x' from (
Select  T1.ID,T1.A,T1.B,T2.C,ROW_NUMBER() over (partition by t1.id order by     
t1.a,t1.b ,t1.c ) as s
From xyz T1 JOIN xyz T2 on T1.ID = T2.ID ) abc ) xyz
where x = 1

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Why do you want to use self join.

Tip: 'A' + NULL=NULL

select ID,max(A) A,max(B) B,max(C) C
from XYZ
where A+B+C is null
group by ID

SQL FIDDLE

Upvotes: 2

Szymon
Szymon

Reputation: 43023

I guess you want something like that:

select T1.ID, T1.A, T1.B, T2.C from XYZ T1
inner join XYZ T2
on T1.ID = T2.ID
where T1.A is not null and T1.B is not null and T2.C is not null

As the join to itself will give you all the combinations of T1 and T2, you want the one where all columns are not null.

Upvotes: 0

Related Questions