Help optimizing this query
-
Hi,
I have two tables, Customer (customerid, name) and Subscription (magazineid, customerid). I have a page where users can search for customers who have subscribed for selected magazines. Say there are 5 magazines, user can select magazine 1 and 3 and hit search, I return all the users who have subscribed for magazine 1 and 3. The way I am doing it is, pass the magazine ids as a comma seperated string. In the stored procedure I split the string and for each magazine id I make sure the customerid exists.
SELECT customerid, name FROM customers cs
WHERE EXISTS (SELECT * FROM subcriptions sub where sub.customerid = cs.customerid and sub.magazine = 1)
AND EXISTS (SELECT * FROM subcriptions sub where sub.customerid = cs.customerid and sub.magazine = 3)
Is there any way to simplify this query. There are lots of customers and around 30 subscriptions. This is actually a part of a larger query.
Thanks.
-
Two possible approaches might be:
SELECT customerid, name
FROM customers cs
WHERE EXISTS( SELECT *
FROM subcriptions sub
WHERE sub.customerid = cs.customerid
AND sub.magazine IN( 1, 3))
SELECT customerid, name
FROM customers cs
INNER JOIN( SELECT sub.customerid
FROM subcriptions sub
WHERE sub.magazine IN( 1, 3)) ON( sub.customerid = cs.customerid)
I wasn't born stupid - I had to study.
-
Well, the result should contain customers who have subscribed for both 1 and 3. If we use IN, won't the result cotains customers who have subscribed for either 1 or 3?
-
Repeating multiple EXISTS for a large number of potential parameters isn't really necessary if you know the number of magazine parameters passed in. All you need to do is count the subscriptions in 1 derived table and only select the records where the count matches.
Assuming you have stored the result of parsing your comma separated params in a variable called @NumberParams, this should work:
SELECT customerid, name
FROM customers cs
INNER JOIN
(
SELECT customerid, Count(*) As NumberSubscribed
FROM subcriptions
WHERE magazine IN (1,3)
GROUP BY customerid
) dt
ON cs.customerid = dt.customerid
WHERE dt.NumberSubscribed = @NumberParams
-
Your original post does imply that - sorry.
You can just add an AND to my suggestion(s):
WHERE sub.magazine = 1
AND sub.magazine = 3
I wasn't born stupid - I had to study.
-
Also, your requirements didn't mention what to return in the above example for a customer who subscribes to, say, magazines 1, 3 and 4 ?
If you pass 1 & 3, do you want to return customers who subscribe ONLY to 1 & 3, or customers who subscribe to AT LEAST 1 & 3 and possibly some others.
-
MUCH better solution and excellent question!
Thanks PW.
I wasn't born stupid - I had to study.
-
"If you pass 1 & 3, do you want to return customers who subscribe ONLY to 1 & 3, or customers who subscribe to AT LEAST 1 & 3 and possibly some others."
Customers who subscribe to atleast 1 and 3.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply