My contact
tràI'm having trouble creating a query for this problem... I have 3 tables:
tblUser
(int primary key) id
(varchar) name
tblGroup
(int primary key) id
(varchar) name
tblUserGroup
(int) group_id
(int) user_id
unique index on group_id, user_id
Users can belong to more than one group so tblUserGroup is a many to many relationship.
If I'm given a selection of user ids that are from tblUser e.g. (45,23,854,2,96) how do I select the group from tblGroup which is made up of the most number of users from the selection, but also only contains users which are in the selection? There are several hundred thousand groups and the query needs to be fast... i've come up with one way but it's pretty slow.
SELECT tblGroup.id, tblGroup.name
FROM tblGroup
INNER JOIN tblUserGroup
ON tblUserGroup.group_id = tblGroup.id AND tblUserGroup.user_id IN (45,23,854,2,96)
LEFT JOIN tblUserGroup AS tblUserGroup2
ON tblUserGroup.group_id = tblGroup.id AND tblUserGroup2.user_id NOT IN (45,23,854,2,96)
WHERE tblUserGroup2.group_id IS NULL
GROUP BY tblGroup.id
Is there a better way to do this? I'm willing to reorganize the tables entirely if many to many is not the best way to go.