Tricky SELECT problem with WHERE and HAVING

[steph2004]

Hi,

I'm having problem bulding the right SQL select with tricky conditions.

For example, let's say I want the number of objets by date where (object name is 'something' and the number of command with this object greater than 5) OR (object name is 'something else' and the number of command with this object greater than 25).

Since I can't put count(commandId) > 5 in the WHERE clause, it has to be in the HAVING clause. But now, I have logic problem. How can I be sure to respect the right statement of the condition?

I thought to put everything in the Having clause but since I have to group object name, the results are not what I expect.

I also tried to do (select count(commandId)) > 5 in the WHERE clause but again, the results are not what's expected.

So, what I would want to do is something like that:

SELECT count(name), objectId, date FROM tbl_objects, tbl_commands WHERE
tbl_objects.id = tbl_commands.objectId AND
(name = 'name1' and count(commandId) > 5) OR
(name = 'name2' and count(commandId) > 25)
GROUP BY name, date

Indeed, this is not working. Using HAVING clause creates unwanted results because the conditionnal logic is not respected. I have to build a generic SQL SELECT because it might have a lot of different conditions which I don't know in advance.

Any idea of how to solve this?

Thanks

Stephane

 

[PHV]

Something like this ?
SELECT C.objectId, O.name, C.date, COUNT(*)
FROM tbl_commands C INNER JOIN tbl_objects O ON C.objectId = O.id
GROUP BY C.objectId, O.name, C.date
HAVING COUNT(*) > (
CASE O.name
WHEN 'name1' THEN 5
WHEN 'name2' THEN 25
END )

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[steph2004]

Yeah, it might do the job!

Thanks a lot!

Stephane