count records in a left joined table where ... 1

[harmmeijer]

I have 3 tables:
course having
CourseID -> int auto incr
CourseDescription -> varchar

CourseInstance having
CourseInsanceID -> int auto incr
CourseID (foreign key to course) -> int
start -> date and time this instance of the course starts
end -> date and time this instance of the course ends
seats -> number of people that can follow this course

CourseInstanceUser (links users to CourseInstance)
userID (foreighn key to users)
CourseInstanceID (foreighn key to CourseInstance)
cancelled -> bit indicating the user cancelled

users
userID -> int auto increment
name -> varchar

I am not interested in who is follwing the courseInstances I want to list all course instances of a certain time period and display the seats available.
The course instance has for example 6 seats, if 3 people registered for this course than 3 seats are available. The sql query for that would be:

select c.ID as 'CourseID',
	c.[CourseDescription] as 'CourseDescription',
	ci.[CourseInsanceID] as 'CourseInstanceId',
	ci.seats as 'seats',
	ci.start as 'start',
	ci.end as 'end',
	seats - count(ciu.[Network ID]) as 'available'
from tblCourses c
join tblCourseInstances ci
on c.ID = ci.CourseID
left join tblCourseInstancesUsers ciu
on ci.ID = ciu.CourseInstanceID
group by c.ID ,
	c.CourseDescription ,
	ci.CourseInsanceID ,
	ci.location ,
	ci.seats ,
	ci.start ,
	ci.end

No problem there (yet). Now take that same example:
courseInstance has got 6 seats, 3 people registered but 1 cancelled (meaning the left joined CourseInstanceUser has one record where cancelled <> 0)
When I add the where statement though I get no records from course instances where nobody has registered for.

select c.ID as 'CourseID',
	c.[CourseDescription] as 'CourseDescription',
	ci.[CourseInsanceID] as 'CourseInstanceId',
	ci.seats as 'seats',
	ci.start as 'start',
	ci.end as 'end',
	seats - count(ciu.[Network ID]) as 'available'
from tblCourses c
join tblCourseInstances ci
on c.ID = ci.CourseID
left join tblCourseInstancesUsers ciu
on ci.ID = ciu.CourseInstanceID

-- added whre statement, don't need to count people who cancelled
where ciu.cancelled <> 0 or ciu.cancelled = null -- null for courseinstances where nobody has 
					-- registered for (yet) or so I would think
group by c.ID ,
	c.CourseDescription ,
	ci.CourseInsanceID ,
	ci.location ,
	ci.seats ,
	ci.start ,
	ci.end

If I add the ciu.cancelled in my select I get the same course instance twice (once for (a) user(s) that cancelled and once for (a) user(s) that didn't cancel.

So how can I get every course instance with the correct amount of available seats?

I hope my question is cleare if not thanks anyway for reading it.



Greetings, Harm Meijer

 

[NoCoolHandle]

[blue]= null[/blue] doesn't work without the correct ansi settings

use [blue]is null[/blue]to find null values and if you want to [blue]sum or count[/blue] when a column could be null it is possible, but you must use the sum([blue]isnull([/blue]col,0[blue]))[/blue] to make a null a valid number.
Othewise nulls are dropped from aggreagates.


at that point you could use the isnll to always be 0 if no activiy is and then you could easily subtract the nulls from the leftouther join.

HTH

Rob
Rob

 

[harmmeijer]

Thank you, I use the statement as above without the = null (should have been IS NULL)
where ciu.[cancelled] IS null or ciu.[cancelled] <> 0




Greetings, Harm Meijer

 

[harmmeijer]

^%&*((^^% wrong again:
where ciu.[cancelled] IS null or ciu.[cancelled] = 0


Now it is solved, need to count the records where cancelled is 0 or null zero in case of a registered user that didn't cancel. NULL in case of a courseInstance that nobody registered for.



Greetings, Harm Meijer