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
 General SQL Server Forums
 New to SQL Server Programming
 match returns no records, no match exact 1 record

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 =@seluser
union all
select NULL, NULL,@seluser)a

WHERE (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
END
ELSE
SELECT NULL AS ID ,
NULL AS number ,
@seluser AS username
Go to Top of Page

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
)
Go to Top of Page

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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:20:25
thats same as this


select null as id
,null as number
,@seluser
where not exists (select *
from ticket
where username=@seluser
)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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!
Go to Top of Page

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 thing

you dont need * inside subquery but just select 1 would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -