Problems retrieving values where count = 0

[Buchead]

Hello,

 

Been given a database that I'm trying to extract data from 3 of the fields. The ones in question are:

 

`bookings` - bookID, pitchID, gDate, gTime

`pitches` - pitchID,name,location

`pitch_tree` - pitchID, sectionID

 

The result needed is dependant upon the search criteria entered. If the end-user only searches on a pitch name it's easy, however they could search on a section (there are a few, each one a different sport and on a given date.  Each pitch has got 10 time slots throughout the day, so I need to return all the pitches that are in a certain section and either have no bookings or free slots on that date. If no bookings have been made then there will be no enteries in `booking`.

 

Built up the following query:

 

SELECT p.* FROM `pitches` AS p LEFT JOIN `bookings` AS b ON p.pitchID=b.pitchID LEFT JOIN `pitch_tree` AS t ON p.pitchID=t.pitchID
WHERE (b.gDate != '2007-11-05' OR COUNT(b.gDate) = 0) AND t.sectionID='2'

 

Clearly it doesn't work. Have tried grouping by gDate values and pitchID to no avail. Different joins and orders of the tables also has no effect.

 

Can someone please point out where I've messed up.

 

Thanks,

 

Clive.

[fenway]

You can't use aggregate functions in the WHERE clause.

[Buchead]

Is there any way of achieving the required result in a single query then?

 

Thanks.

[fenway]

Move it to the HAVING clause... and that != won't engage the index.

[svivian]

You wanna have your 'count' function in the SELECT clause, then a conditional on that in a HAVING clause. The basic format is:

 

SELECT COUNT(field) AS total FROM table HAVING total>50