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.
Author |
Topic |
barnabeck
Posting Yak Master
236 Posts |
Posted - 2013-05-01 : 17:56:16
|
I need to create a query that returns exactly one record in case that a username is not in the database, and that does not return any record in any other case. The following code should do the job, but the reference to the inner junction in the WHERE clause throws an error that I don't know how to correct.select a.* from (select ID, number, username from tickets WHERE username =@seluserunion all select NULL, NULL,@seluser)aWHERE (SELECT count(a.username) from tickets where username =@seluser) =1 and username =@seluser The error it throws is:An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.Anyone can give me a hint?thanks, Martin |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-01 : 18:39:38
|
As the error message indicates, you cannot use an aggregate in the WHERE clause. Instead, put it in a HAVING clause. That said, I didn't follow the logic you are trying to implement. I would have done it something like shown below:IF EXISTS ( SELECT * FROM tickets WHERE username = @seluser ) BEGIN SELECT ID , number , username FROM tickets WHERE username = @seluser ENDELSE SELECT NULL AS ID , NULL AS number , @seluser AS username |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-05-01 : 19:07:39
|
or maybe:select null as id ,null as number ,username from (select @seluser as username) where not exist (select * from ticket where username=@seluser ) |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-01 : 20:36:31
|
bitsmed's solution works with the following minor syntax correction:[CODE]select null as id ,null as number ,username from (select @seluser as username)as A where not exists (select * from ticket where username=@seluser )[/CODE]Nice job, bitsmed! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-02 : 01:20:25
|
thats same as thisselect null as id ,null as number ,@seluser where not exists (select * from ticket where username=@seluser ) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
barnabeck
Posting Yak Master
236 Posts |
Posted - 2013-05-02 : 04:57:12
|
Thank you for the different solutions.@James K: this doesn't do what I was looking for; in case of a match for username =@seluser the query returns those records, while it shouldn't return any. My logic was to add (union all) one record to the database (NULL, NULL, @seluser) and find only those record where count(username) for @seluser = 1.@bitsmed & @MuMu88: Works perfect with the small corrections proposed by MuMu88@visakh16: Works perfect. Synthetic and compact as usual! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-02 : 05:01:42
|
quote: Originally posted by barnabeck Thank you for the different solutions.@James K: this doesn't do what I was looking for; in case of a match for username =@seluser the query returns those records, while it shouldn't return any. My logic was to add (union all) one record to the database (NULL, NULL, @seluser) and find only those record where count(username) for @seluser = 1.@bitsmed & @MuMu88: Works perfect with the small corrections proposed by MuMu88@visakh16: Works perfect. Synthetic and compact as usual!
One more thingyou dont need * inside subquery but just select 1 would do------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|