INNER JOIN on same table
INNER JOIN on same table
(OP)
Given table tblA
A varchar(100), B int, C int
A B C
100 | 1 | 1
100 | NULL | 2
100 | NULL | 3
200 | 2 | 2
300 | 3 | 1
300 | NULL | 2
400 | 4 | 1
400 | NULL | 2
400 | NULL | 3
500 | 5 | NULL
500 | 6 | NULL
How do I return all records where C IS NOT NULL but with it's corresponding B value for the A record. So for example
A B C
100 | 1 | 1
100 | 1 | 2
100 | 1 | 3
200 | 2 | 2
300 | 3 | 1
300 | 3 | 2
400 | 4 | 1
400 | 4 | 2
400 | 4 | 3
Thanks
A varchar(100), B int, C int
A B C
100 | 1 | 1
100 | NULL | 2
100 | NULL | 3
200 | 2 | 2
300 | 3 | 1
300 | NULL | 2
400 | 4 | 1
400 | NULL | 2
400 | NULL | 3
500 | 5 | NULL
500 | 6 | NULL
How do I return all records where C IS NOT NULL but with it's corresponding B value for the A record. So for example
A B C
100 | 1 | 1
100 | 1 | 2
100 | 1 | 3
200 | 2 | 2
300 | 3 | 1
300 | 3 | 2
400 | 4 | 1
400 | 4 | 2
400 | 4 | 3
Thanks
RE: INNER JOIN on same table
FROM yourTable A INNER JOIN yourTable B ON A.A=B.A
WHERE A.C IS NOT NULL AND B.B IS NOT NULL
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?