Find a user who has looked at these 3 products? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Find a user who has looked at these 3 products?

Author

Topic

morganeason
Starting Member

4 Posts

Posted - 2007-08-24 : 14:08:36

Hi all,

So I have a table which records all the skus a particual user has looked at.
UserId - Sku

How can I see all the users who have looked at a particual three skus? I need them to have looked at all three skus not any of the skus.

I tried using a 2 JOINs back onto the table but that was really inefficient. I thought of using a IN() but that will give me results for people that have look at just 1 of the 3.

What am I missing?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 14:12:55

SELECT UserID
FROM Table1
WHERE SKU IN ('SKU1', 'SKU2', 'SKU3')
GROUP BY UserID
HAVING COUNT(DISTINCT SKU) = 3

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-24 : 14:14:44

SELECT UserID
FROM Table1
WHERE SKU IN ('SKU1', 'SKU2', 'SKU3')
GROUP BY UserID
HAVING MAX(CASE WHEN SKU = 'SKU1' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN SKU = 'SKU2' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN SKU = 'SKU3' THEN 1 ELSE 0 END) = 1

E 12°55'05.25"
N 56°04'39.16"

morganeason
Starting Member

4 Posts

Posted - 2007-08-24 : 14:22:12

Thanks! That is much faster then my multi join query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-25 : 04:05:50

I suspect that too, yes.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources