self referencing table SELECT issue [modified]

[Harvey Saayman]

hi guys... i have a table that references itself! so i came up with the following query

SELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30 
FROM TimeCategories tc1
JOIN TimeCategories tc2
ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI

that returnes only 1 / 5 rows because only the returned row has a value in TCat_AlternateCategory_SI the rest of the entries has NULL which is suposed to be like that any ideas or sugestions on how to get them all returned (incl rows with NULL in TCat_AlternateCategory_SI)? thanx [UPDATE] i now have this as my querySELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30 FROM TimeCategories tc1 JOIN TimeCategories tc2 ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI UNION SELECT TCat_ID_SI, TCat_Name_VC30, TCat_Rate_D, TCat_Normal_B, 'N/A' FROM TimeCategories WHERE TCat_AlternateCategory_SI = null
but now the line WHERE TCat_AlternateCategory_SI = null isnt working! any sugestions? thanx

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL think BIG and kick ASS

you.suck = (you.passion != Programming)

modified on Friday, February 15, 2008 9:57 AM

[andyharman]

Replace "JOIN" in your first query with "LEFT OUTER JOIN".

[Harvey Saayman]

it worked! thanx man, o u one

Harvey Saayman - South Africa Junior Developer .Net, C#, SQL think BIG and kick ASS

you.suck = (you.passion != Programming)