Buchead Posted November 6, 2007 Share Posted November 6, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/76238-problems-retrieving-values-where-count-0/ Share on other sites More sharing options...
fenway Posted November 6, 2007 Share Posted November 6, 2007 You can't use aggregate functions in the WHERE clause. Quote Link to comment https://forums.phpfreaks.com/topic/76238-problems-retrieving-values-where-count-0/#findComment-385902 Share on other sites More sharing options...
Buchead Posted November 6, 2007 Author Share Posted November 6, 2007 Is there any way of achieving the required result in a single query then? Thanks. Quote Link to comment https://forums.phpfreaks.com/topic/76238-problems-retrieving-values-where-count-0/#findComment-385905 Share on other sites More sharing options...
fenway Posted November 6, 2007 Share Posted November 6, 2007 Move it to the HAVING clause... and that != won't engage the index. Quote Link to comment https://forums.phpfreaks.com/topic/76238-problems-retrieving-values-where-count-0/#findComment-385922 Share on other sites More sharing options...
svivian Posted November 6, 2007 Share Posted November 6, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/76238-problems-retrieving-values-where-count-0/#findComment-386073 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.