Clever Geek Handbook

Combines and where conditions

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 WHERE

they don't work FlagCount = 0

or COUNT(Flags.FlagType)

. I was thinking about using it SET

, but I'm not sure how to add this to the request. Any ideas?

Thank,

+1


source to share


4 answers


Maybe you can try subquery 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
+2


source


Use HAVING COUNT(DISTINCT Flags.FlagType) = 0

.



+5


source


Try the following:

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
0


source


@ eed3si9n

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

Also a condition is met HAVING

that doesn't return as many results as I need (as set LIMIT

). Is there a way to do this in a sentence WHERE

instead?

0


source






More articles:


All Articles