SQL Server Performance Forum – Threads Archive

Null Match Prob

Hi i am havin a bit of trouble tryin to get a match based on a null value in a table, there are 4 tables based on the id that is on the bottom table which is null in some of them, i use dif ids to get to the top table and use the namethat is in there this is the code i am using i am just wondering ifanyone has any ideas of how to work around this problem select ssg.Name + ‘ ‘ + supt.Name + ‘ ‘ + st.Name as name, st.name as ‘4th’, supt.name as ‘3rd’, ssg.name as ‘2nd’, sg.name as ‘1st’
from session s
left join sessionActivity sa on sa.SessionId = s.Id
left join SessionType as st on st.ID = sa.SessionType
left join SessionSuperType as supt on supt.ID = st.SuperTypeId
left join SessionSubGroup as ssg on ssg.ID = supt.SessionSubGroupId
left join SessionGroup as sg on sg.ID = ssg.SessionGroupId
where sg.Id <> 8
and sg.Id <> 9
and s.ID = 50

I might be wrong, but try to put the WHERE clause into the JOIN condition. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

If you want to join two tables on a column that contains NULL, and you want the NULLs to match, you have a few options: SELECT t1.*, t2.*
FROM t1, t2
WHERE ((t1.column = t1.column)
OR (t1.column IS NULL AND t2.column IS NULL)) SELECT t1.*, t2.*
FROM t1, t2
WHERE ISNULL(t1.column, -1) = ISNULL(t1.column, -1) SELECT t1.*, t2.*
FROM t1 INNER JOIN t2
ON ((t1.column = t1.column)
OR (t1.column IS NULL AND t2.column IS NULL)) SELECT t1.*, t2.*
FROM t1 INNER JOIN t2
ON ISNULL(t1.column, -1) = ISNULL(t1.column, -1)

I think you are right Frank: select ssg.Name + ‘ ‘ + supt.Name + ‘ ‘ + st.Name as name, st.name as ‘4th’, supt.name as ‘3rd’, ssg.name as ‘2nd’, sg.name as ‘1st’
from session s
left join sessionActivity sa on sa.SessionId = s.Id
left join SessionType as st on st.ID = sa.SessionType
left join SessionSuperType as supt on supt.ID = st.SuperTypeId
left join SessionSubGroup as ssg on ssg.ID = supt.SessionSubGroupId
left join SessionGroup as sg on sg.ID = ssg.SessionGroupId and sg.Id <> 8 and sg.Id <> 9
where s.ID = 50
I must admit I didn’t quite understand the question, but regardless the answer might be the code I posted based on Franks idea.

I guess Adriaan figured out what the question is about. The conclusion is that you should start with query I posted if you really need outer join on sessionGroup table and then apply technique described by Adriaan.

[xx(] The usual request for DML, sample data and required output would have been better. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

By the way, be careful with the ISNULL function – you must choose a substitue value (2nd parameter) that does not occur in the actual data, otherwise you get false matches.

thanks alot guys but it doesnt seem to be workin, sorry about this now but either i dont understand or ye dont and im really not sure but here goes First Table (Top)
ID Name MatchID
2 FT 11 Second Table
ID Name MatchID
11 Weight 26 Third Table
ID Name MatchID
26 NULL 99 Fourth Table
ID Name
99 NULL so as there is no name in the fourth table i need to return the name in the third table but as there is also no name here i need to return the name in second table, but there will be names in the fourth talbe for othere ids which will match up without any probs
does this help you guys much

There’s a special function you can use called COALESCE, which will take any number of parameters, and it returns the first parameter that is not null. select
COALESCE(t4.name, t3.name, t2.name, t1.name)
from t1 left join t2 on t1.matchid = t2.id
left join t3 on t2.matchid = t3.id
left join t4 on t3.matchid = t4.id Another option is to use a CASE construct – it takes a bit more work, but it may perform better than COALESCE on large amounts of rows: select
CASE WHEN t4.name IS NOT NULL THEN t4.name
ELSE (CASE WHEN t3.name IS NOT NULL THEN t3.name
ELSE (CASE WHEN t2.name IS NOT NULL THEN t2.name ELSE t1.name END) END) END
from t1 left join t2 on t1.matchid = t2.id
left join t3 on t2.matchid = t3.id
left join t4 on t3.matchid = t4.id

i think thats exactly what i needed thanks a million i really appreciate it man

]]>