INNER JOIN on same table 1

Laeg · Mar 9, 2009

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

PHV · Mar 9, 2009

SELECT A.A,B.B,A.C
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
FAQ181-2886

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

INNER JOIN on same table 1

Laeg

Programmer

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor