March 27, 2012 at 10:47 am
with out DDL and sample data from the UserID table your populating your temp tables from it would be a little tough. This may work though:
SELECT u1.userID FROM Users u1
INNER JOIN Users u2
ON u1.userID = u2.userID
AND u2.category = 2
AND u2.active = @active
AND u2.paying = @pay
INNER JOIN Users u3
ON u1.userID = u3.userID
AND u3.category = 3
AND u3.active = @active
AND u3.paying = @pay
INNER JOIN Users u4
ON u1.userID = u4.userID
AND u4.category = 4
AND u4.active = @active
AND u4.paying = @pay
WHERE u1.category = 1
AND u1.active = @active
AND u1.paying = @pay
Table DDL and sample data along with what your results would be will provide a better answer as we have something to chew on.
EDIT: copy and paste error
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 28, 2012 at 3:13 am
I just needed an idea on how should I approach differently the query, more optimized. I will try it with your suggestion.
Thank you
March 28, 2012 at 4:59 pm
shnex (3/28/2012)
I just needed an idea on how should I approach differently the query, more optimized. I will try it with your suggestion.Thank you
no problem. self joins can get a bit complicated but i take the approach of which sets do i want to compare write a query to get that set out the figure out the self joins to compare them all. gets rid of temp tables and lets the query optimizer have its way.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply