Tricky SELECT problem with WHERE and HAVING

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tricky SELECT problem with WHERE and HAVING

Author

Topic

steph2004
Starting Member

3 Posts

Posted - 2006-08-14 : 17:21:11

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-14 : 18:14:06

You have objectId in the select list, but you are not grouping by it, so that will not work.

Maybe you want something like this.


SELECT
	name,
	date,
	[objectId Count] = count(*)
FROM
	tbl_objects
	join
	tbl_commands
	on tbl_objects.id = tbl_commands.objectId
WHERE
	name in ('name1','name2')
having
	(name = 'name1' and count(*) > 5) OR
	(name = 'name2' and count(*) > 25)
GROUP BY
	name,
	date

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-08-14 : 18:28:08

SELECT count(name), objectId, date ...
...etc...
GROUP BY name, date

does not make sense!

If you have many objectId's per name then:

SELECT name, count(objectId), date ...
...etc...
GROUP BY name, date

Could make sense, but then I would question why name is not UNIQUE in tbl_objects ?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources