self join not returning null 1

[chaoma]

Hello,

I have a data like this:

ID Program Status
1 Fed AP
1 Stt AP
2 Cal AP
2 Stt DN
3 Fed AP

I want my data to be :

ID Program Status Stt_Status
1 Fed AP AP
2 Cal AP DN
3 Fed AP Null

On ID=3, there isn't any Stt; therefore, I want it to display Null

Here is my query:

Select a.ID,
a.Program,
a.status,
b.stt_status
from table a, table a on (a.ID=b.ID)
and a.Program in (Fed, Cal)
and b.Program='Stt'

I am missing ID=3. What am I doing wrong?

Thank you.

Chaoma

 

[PHV]

You have to use a LEFT OUTER JOIN:
SELECT a.ID, a.Program, a.Status, b.Status Stt_Status
FROM table a
LEFT JOIN table b ON a.ID=b.ID AND b.Program='Stt'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chaoma]

I am sorry, it was a typo error. I did use left join, and it did not give me ID=3.

Thanks.

Chaoma.

 

[PHV]

So, what is your real SQL code ?

 

[chaoma]

Here it is:

Select A.ID,
A.Program,
A.status,
B.stt_status
from table A left join table B on (A.ID=B.ID)
and A.Program in (Fed, Cal)
and B.Program='Stt'

Thanks.

 

[PHV]

Where is B.stt_status coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chaoma]

Sorry, another typo.

Here it is again:

Select A.ID,
A.Program,
A.status,
B.status
from table A left join table B on (A.ID=B.ID)
and A.Program in (Fed, Cal)
and B.Program='Stt'

Sorry, I am using two PCs. I tried to copy the text from another PC.

Sorry, sorry.

Thanks.

 

[PHV]

The following is suspect too:
and A.Program in ([!]Fed[/!], [!]Cal[/!])
So, again, what is the REAL code ?
Are you sure you don't have any WHERE clause defeating the OUTER join purpose ?
And, just to know, which RDBMS ?

Another way you may try:
SELECT a.ID, a.Program, a.Status, b.Stt_Status
FROM table a LEFT JOIN (
SELECT ID, Status Stt_Status FROM table WHERE Program='Stt'
) b ON a.ID = b.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chaoma]

PHV,

Thank you. I have to check it tomorrow morning. Going home from work.

Again thank you.

Chaoma

 

[chaoma]

PHV,

Thank you. Your SQL code work like a charm. I learn something today. Oh, I am using SQL 2000.

Chaoma