January 17, 2006 at 3:39 am
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 if anyone 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
January 17, 2006 at 5:40 am
Based on SQL-92 standards, null is not equal to null. (As null means, not defined or unknown) However, SQL Server offers another feature to turn off this option by issueing SET ANSI_NULLS OFF. However, after running the query you may have to turn this option On again. Due to this reason, it is not advisable.
By suggestion is to add the additional condition to check whether both are null or both are requal. It may look some thing like this:
sa.SessionID = s.ID OR (sa.SessionID is NULL AND s.ID is NULL)
You can even try ISNULL(sa.SessionID, 0) = ISNULL(s.ID, 0) if both fields can hold only non zero values.
Hope this may help you,
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
January 17, 2006 at 7:13 am
Not sure what you mean by match prob. Can you post ddl and sample data plus expected output?
I would write your query like this
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 and ssg.SessionGroupId NOT IN (8,9)
left join SessionGroup as sg on sg.ID = ssg.SessionGroupId
where s.ID = 50
Not sure if this helps or not
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply