开发者

Alternative for using Intersect operator in my sql query

开发者 https://www.devze.com 2023-04-09 22:14 出处:网络
I have 2 tables with many to many cardinality between them. So by normalization I have created this :

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号