August 14, 2006 at 3:23 pm
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
August 14, 2006 at 3:34 pm
Can you post your DDL for the two tables, some sample data for each table, and your expected result set based off of the sample data?
August 14, 2006 at 3:56 pm
SELECT o.name, c.date, count(*)
FROM tbl_objects As o
INNER JOIN tbl_commands As c
ON (o.id = c.objectId)
WHERE EXISTS (
SELECT 1
FROM tbl_commands As c2
WHERE o.Name = 'name1'
AND o.id = c2.object_id
HAVING Count(c2.commandid) > 5
)
OR EXISTS (
SELECT 1
FROM tbl_commands As c3
WHERE o.Name = 'name2'
AND o.id = c3.object_id
HAVING Count(c3.commandid) > 25
)
GROUP BY o.name, c.date
August 14, 2006 at 4:43 pm
SELECT count(name), objectId, date
FROM tbl_objects
INNER JOIN tbl_commands ON tbl_objects.id = tbl_commands.objectId
WHERE name = 'name1'
GROUP BY name, date
HAVING count(commandId) > 5
UNION
SELECT count(name), objectId, date
FROM tbl_objects
INNER JOIN tbl_commands ON tbl_objects.id = tbl_commands.objectId
WHERE name = 'name2'
GROUP BY name, date
HAVING count(commandId) > 25
_____________
Code for TallyGenerator
August 14, 2006 at 5:06 pm
Thanks guys for your answers.
I thinks this will do the job:
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 )
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply