working with null values

hi guys ... null values are jhandled differently by SQL 2000 right!

i have this 2 tables who are related by fields SubjectID and ProgramID . the problem  is programids can be null (blame the dbase designer!) so to query for all subjects i usually use union to join tables with null programids and those that does have values for the programid

heres the sample code

SELECT DISTINCT sample.dbo.Class.CodeNo, sample.dbo.Subject.SubjectID,sample.dbo.Subject.SubjectTitle,
        sample.dbo.Subject.LabUnits ,sample.dbo.Subject.LecUnits,sample.dbo.Subject.TFR
    FROM    sample.dbo.Class
        INNER JOIN sample.dbo.Codes ON sample.dbo.Class.CodeNo=sample.dbo.Codes.CodeNo and sample..Class.SchoolYear=sample..Codes.SchoolYear
        INNER JOIN sample.dbo.Subject ON sample.dbo.Subject.SubjectID=sample.dbo.Codes.SubjectID
        INNER JOIN sample.dbo.Curriculum ON (sample.dbo.Subject.ProgramID=sample.dbo.Curriculum.ProgramID and            sample.dbo.Codes.ProgramID=sample.dbo.Subject.ProgramID)   
UNION
   
SELECT DISTINCT sample.dbo.Class.CodeNo, sample.dbo.Subject.SubjectID,sample.dbo.Subject.SubjectTitle,
        sample.dbo.Subject.LabUnits ,sample.dbo.Subject.LecUnits,sample.dbo.Subject.TFR
    FROM  sample.dbo.Class
        INNER JOIN sample.dbo.Codes ON sample.dbo.Class.CodeNo=sample.dbo.Codes.CodeNo and sample..Class.SchoolYear=sample..Codes.SchoolYear
          INNER JOIN sample.dbo.Subject ON sample.dbo.Subject.SubjectID=sample.dbo.Codes.SubjectID and sample.dbo.Codes.ProgramID is null and sample.dbo.subject.programid is null
   
 
is there any other way to go around this without using UNION.

i tried posting this to msforums but the solutions presented was not working so far...http://msforums.ph/forums/136668/ShowPost.aspx
thanks

Can you try this?

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle, 
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
INNER JOIN
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
LEFT JOIN
 sample.dbo.Curriculum d
  ON  (
    c.ProgramID = d.ProgramID
     AND b.ProgramID = c.ProgramID
   )  

[/code]

keithrull:

Can you try this?

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle, 
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
INNER JOIN
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
LEFT JOIN
 sample.dbo.Curriculum d
  ON  (
    c.ProgramID = d.ProgramID
     AND b.ProgramID = c.ProgramID
   )  

[/code]

hey bro.. it tested the script you just posted  the number of returned records were almost multiplied by 7.
the curriculum table shld not be included in the testing for programids it should be only within the codes and subject table  .

thanks anyway...hope to find the solution in several days im looking at possiblities of using 2 views to separate the codes with nul and thoses that does not have null values...

keithrull:

Can you try this?

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle, 
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
INNER JOIN
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
LEFT JOIN
 sample.dbo.Curriculum d
  ON  (
    c.ProgramID = d.ProgramID
     AND b.ProgramID = c.ProgramID
   )  

[/code]

hey bro.. i tested the script you just posted  the number of returned records were almost multiplied by 7.
the curriculum table shld not be included in the testing for programids it should be only within the codes and subject table  .

thanks anyway...hope to find the solution in several days im looking at possiblities of using 2 views to separate the codes with nul and thoses that does not have null values...

trashVin:

keithrull:

Can you try this?

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle, 
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
INNER JOIN
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
LEFT JOIN
 sample.dbo.Curriculum d
  ON  (
    c.ProgramID = d.ProgramID
     AND b.ProgramID = c.ProgramID
   )  

[/code]

hey bro.. i tested the script you just posted  the number of returned records were almost multiplied by 7.
the curriculum table shld not be included in the testing for programids it should be only within the codes and subject table  .

thanks anyway...hope to find the solution in several days im looking at possiblities of using 2 views to separate the codes with nul and thoses that does not have null values...

I see... i think i just followed the first select on top when i extracted this query. will try again later :)

Try this:

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle,
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
LEFT JOIN 
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
  AND c.ProgramID = b.ProgramID
LEFT JOIN
 sample.dbo.Curriculum d
  ON  (
    c.ProgramID = d.ProgramID
   )  

[/code]

And also this:

[code language="T-SQL"]

SELECT DISTINCT
 a.CodeNo,
 c.SubjectID,
 c.SubjectTitle,
 c.LabUnits,
 c.LecUnits,
 c.TFR
FROM   
 sample.dbo.Class a
INNER JOIN
 sample.dbo.Codes b
  ON a.CodeNo = b.CodeNo
   AND a.SchoolYear = b.SchoolYear
LEFT JOIN 
 sample.dbo.Subject c
  ON c.SubjectID = b.SubjectID
  AND c.ProgramID = b.ProgramID

[/code]

I have removed the INNER JOIN  on Curriculum since it is not being used on our select statement.