INNER JOIN on same table 1

[Laeg]

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]

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