Hello

Just trying to Join two tables along with ordering and counting from one table I have gotten so far but not close enough lol. Here is what I have right now.


SELECT `orders`.`Date`, COUNT(`orders`.`Date`) As `Count`, `franchises`.`Calendar`
FROM `orders`
JOIN `franchises` ON `franchises`.`ID` = `orders`.`Franchise`
WHERE `orders`.`Date` <> '' && `orders`.`Date` > :DATE && `orders`.`Franchise` = :FRANCHISE
GROUP BY `orders`.`Date`
HAVING COUNT(`orders`.`Date`) < `franchises`.`MaxOrders`
ORDER BY `orders`.`Date`


The bit I get an error on is the HAVING clause which I need to obtain the max-orders which the specific franchise has set which could be like '5' orders at max, now I need to count the amount of jobs which are per each date so like on '20-10-2011' could have '3' jobs and '21-10-2011' could have '10' jobs now if the franchise has set their max-orders to '10' i don't want the second date to show up in the results has that has hit the max amount of jobs on a date, how can I do this?

Thank you
- DJCMBear

You have been suggested to use date format for Date in one of previous posts.
Do as suggested. Allso you have been adviced not to use field names equal to mysql functions.
You can, but things are then confusing.


mybe, if error, which you don't tell what it is

HAVING `Count` < `franchises`.`MaxOrders`

out of head.

You have been suggested to use date format for Date in one of previous posts.
Do as suggested. Allso you have been adviced not to use field names equal to mysql functions.
You can, but things are then confusing.


mybe, if error, which you don't tell what it is

HAVING `Count` < `franchises`.`MaxOrders`

out of head.


I am using `..` around the names so that doesn't confuse the SQL query also I am using PDO which uses prepare and execute for stopping these types of problems so that is why I am using them and it works perfectly, I have also managed to fix the problem I was having which was using PHP to check like this $row['Count'] >= $row['MaxOrders'] so it works fine now.