working with null values -

ATTENTION: I've decided to put the upgrade on hold due to a compatibility issue of our server environment with the latest CS installer package. CS 2008 now requires SQL Server 2005 as the backend DB but our database server currenlty has SQL Server 2000 installed on it. I'll resume the upgrade once I figure out when Telligent is releasing a patch to the schema compatibility issue. For now, we will continue to use the old version of CS while waiting for the said patch. If you have any questions about this process, please don't hesitate to post them on our forums and I'll answer them as soon as I can. Thanks for your patience and support guys! I'll let you know as soon as this is resolved. - Keith Rull

Latest post 06-02-2006 5:07 PM by keithrull. 5 replies.

Page 1 of 1 (6 items)
	Sort Posts: Previous Next

05-31-2006 1:06 AM

trashVin
Joined on 02-21-2006
Tuguegarao City, Cagayan
Posts 80
Points 1,195

working with null values

Reply Contact

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

Post Points: 20

05-31-2006 10:43 AM In reply to

keithrull
Joined on 08-08-2005
San Diego, CA
Posts 1,956
Points 39,165

Re: working with null values

Reply Contact

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]

Post Points: 35

06-01-2006 9:09 PM In reply to

trashVin
Joined on 02-21-2006
Tuguegarao City, Cagayan
Posts 80
Points 1,195

Re: working with null values

Reply Contact

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...

Post Points: 5

06-01-2006 9:09 PM In reply to

trashVin
Joined on 02-21-2006
Tuguegarao City, Cagayan
Posts 80
Points 1,195

Re: working with null values

Reply Contact

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...

Post Points: 20

06-02-2006 5:01 PM In reply to

keithrull
Joined on 08-08-2005
San Diego, CA
Posts 1,956
Points 39,165

Re: working with null values

Reply Contact

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 :)

Post Points: 5

06-02-2006 5:07 PM In reply to

keithrull
Joined on 08-08-2005
San Diego, CA
Posts 1,956
Points 39,165

Re: working with null values

Reply Contact

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.

Post Points: 5

Page 1 of 1 (6 items)