Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2009-02-26 : 11:34:12
|
| Hi,I have a join on this column that can have null values in it. But I still want those records. Basically it is a self join, trying to find the parents. I tried like:SELECT DISTINCT C.FACILITYSID, C.FacilityCode, C.NAME, FACTYPELABEL,PROVSTATELABEL,P.NAMEFROM FACILITY_INFO C,FACILITYTYPE_CODE,PROVSTATE_CODE, FACILITY_INFO PWHERE C.TYPEID = FACTYPESID AND C.PROVINCEID = PROVSTATESIDAND (C.ParentFacilityID = P.FACILITYSID OR C.ParentFacilityID = NULL)But it still gives me the matching rows.Any help?Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-26 : 11:44:41
|
| Can you use Left Outer Join? |
 |
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2009-02-26 : 12:44:19
|
| Can you show me please?Thanks. |
|
|
|
SQLforGirls
Starting Member
48 Posts |
Posted - 2009-02-26 : 12:57:55
|
| This is how the left join would lookSELECT DISTINCT C.FACILITYSID, C.FacilityCode, C.NAME, FL.FACTYPELABEL,PL.PROVSTATELABEL,P.NAMEFROM FACILITY_INFO PLEFT JOIN FACILITY_INFO C ON P.FACILITYSID = C.ParentFacilityIDJOIN FACILITYTYPE_CODE FC ON C.TYPEID = FC.FACTYPESIDJOIN PROVSTATE_CODE PC ON C.PROVINCEID = PC.PROVSTATESIDBut depending on what you mean when you say there could be NULL records....If this doesn't solve it for you exactly, please post some sample data showing where the NULL values might be. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Join on a column with null values