SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 match returns no records, no match exact 1 record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
190 Posts

Posted - 05/01/2013 :  17:56:16  Show Profile  Reply with Quote
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

Edited by - barnabeck on 05/01/2013 18:08:09

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 05/01/2013 :  18:39:38  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

323 Posts

Posted - 05/01/2013 :  19:07:39  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/01/2013 :  20:36:31  Show Profile  Reply with Quote
bitsmed's solution works with the following minor syntax correction:


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


Nice job, bitsmed!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  01:20:25  Show Profile  Reply with Quote
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

Spain
190 Posts

Posted - 05/02/2013 :  04:57:12  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/02/2013 :  05:01:42  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000