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,
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
Use HAVING COUNT(DISTINCT Flags.FlagType) = 0
.
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
@ 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?