Joins and where conditions

Question

I have the following (shortened query):

SELECT 
    `Statistics`.`StatisticID`,
    COUNT(DISTINCT `Flags`.`FlagType`) AS `FlagCount`
FROM `Statistics`
LEFT JOIN `Flags` ON `Statistics`.`StatisticID` = `Flags`.`StatisticID`
WHERE `FlagCount` = 0
GROUP BY `Statistics`.`StatisticID`
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10

Now, neither FlagCount = 0 or COUNT(Flags.FlagType) work in the WHERE clause. I thought about using a SET but I'm not sure how I'd add that to the query. Any ideas?

Thanks,

Answer 1

Use HAVING COUNT(DISTINCT Flags.FlagType) = 0.

Answer 2

Try this:

SELECT 
    `Statistics`.`StatisticID`,
    COUNT(DISTINCT `Flags`.`FlagType`) AS `FlagCount`
FROM `Statistics`
LEFT JOIN `Flags` ON `Statistics`.`StatisticID` = `Flags`.`StatisticID`
                     And `FlagCount` = 0
GROUP BY `Statistics`.`StatisticID`
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10

Answer 3

@eed3si9n

This partially works - however I need it to be <= 3 which doesn't seem to work.

Also the HAVING clause is executed last which won't return as many results as I need (as set by LIMIT). Is there a way I can do this in a WHERE clause instead?

Answer 4

Maybe you can try subquerying if HAVING doesn't work.

SELECT 
 `Statistics`.`StatisticID`,
 COUNT(DISTINCT `Flags`.`FlagType`) AS `FlagCount`
FROM `Statistics`
 LEFT JOIN `Flags` ON `Statistics`.`StatisticID` = `Flags`.`StatisticID`
WHERE `Statistics`.`StatisticID`
  IN (SELECT `Flags`.`StatisticID` 
   FROM `Flags`
   HAVING COUNT(DISTINCT `Flags`.`FlagType`) <= 3
   GROUP BY `Flags`.`StatisticID`
  )
GROUP BY `Statistics`.`StatisticID`
ORDER BY `SubmittedTime` DESC
LIMIT 0, 10