August 31, 2006 at 3:41 pm
Hi,
I have this search query where I select all the users based on the parameter @iscustomer that is passed into the stored procedure. If @iscustomer is null, I select all the users. If it is 'Y', I select only the users who have placed an order with us (an entry in the order table). If it is 'N', I select only the users who have not placed any order with us before (no entry in the order table). I would like to do this in a single query. is it possible? I tried something like this, but its not working.
..........................................
SELECT * FROM users
WHERE
CASE
WHEN @iscustomer IS NULL THEN @iscustomer = @iscustomer
WHEN @iscustomer = 'Y' THEN EXISTS (SELET * FROM orders WHERE orders.userid = users.userid)
WHEN @iscustomer = 'N' THEN NOT EXISTS (SELET * FROM orders WHERE orders.userid = users.userid)
END
..........................................
Thanks.
August 31, 2006 at 4:00 pm
First I sugeest you avoid using '*' in your select statement. Second, I'm making a few assumptions here, but this should get you started.
select
u.userid
from
users u
left join orders o
on u.userid = o.userid
where
(IsNull(@iscustomer, '') = '') OR
(@iscustomer = 'Y' and IsNull(o.userid, 0) 0) OR
(@iscustomer = 'N' and IsNull(o.userid, 0) = 0)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 31, 2006 at 4:23 pm
If you don't need Case in the WHERE CLAUSE you can use the following:
If @iscustomer is Null
Begin
SELECT * FROM users
End
Else
If @iscustomer='Y'
Begin
Select UserId,UserName from users where exists (SElect 1 from orders WHERE orders.userid = users.userid)
End
Else
Begin
Select UserId,UserName from users where not exists (SElect 1 from orders WHERE orders.userid = users.userid)
End
End
Thanks
Sreejith
August 31, 2006 at 4:36 pm
SELECT u.*
FROM users As u
WHERE IsNull(@iscustomer, 'Y') = 'Y'
AND EXISTS (
SELECT *
FROM orders as o WHERE o.userid = u.userid
)
UNION ALL
SELECT u.*
FROM users As u
WHERE IsNull(@iscustomer, 'N') = 'N'
AND NOT EXISTS (
SELECT *
FROM orders as o WHERE o.userid = u.userid
)
Alternatively
SELECT *
FROM
(
SELECT u.*,
CASE WHEN dt.userid IS NULL THEN 'N' ELSE 'Y' END As IsCustomer
FROM users
LEFT JOIN (
SELECT DISTINCT userid
FROM orders
) dt1
On (u.userid = dt.userID
) dt2
WHERE IsCustomer = IsNull(@IsCustomer, dt2.IsCustomer)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply