Alternative for using Intersect operator in my sql query
I have 2 tables with many to many cardinality between them. So by normalization I have created this :
User
UserId UserName ....
1 a
2 b
UserObject
UserId ObjectId
1 1
1 2
2 2
Object
ObjectId ObjectName
1 c
2 d
Now I want to run a query where I want to know users which have certain objec开发者_JAVA百科ts.
For example : All the users who have both objects c and d.
One way of doing it
Select userid from UserObject where objectid=1 intersect Select userid from UserObject where objectid= 2
According to my use case, I may need to search for users having combination of 2-7 objects. It will not be prudent to write so many intersections.
I am working on postgesql 9.1.
What are the other efficient possible ways to make this happen?
SELECT uo.UserId
FROM UserObject uo
WHERE uo.ObjectId IN (1,2)
GROUP BY uo.UserId
HAVING COUNT(DISTINCT uo.ObjectId) = 2
Extending this concept for 7 objects:
SELECT uo.UserId
FROM UserObject uo
WHERE uo.ObjectId IN (1,2,3,4,5,6,7)
GROUP BY uo.UserId
HAVING COUNT(DISTINCT uo.ObjectId) = 7
精彩评论