Database Discussion - Tricky Query problem

Print Page | Close Window

Tricky Query problem - Many to Many structure.

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=27030
Printed Date: 28 March 2026 at 2:21pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Tricky Query problem - Many to Many structure.

Posted By: ctytrungloi
Subject: Tricky Query problem - Many to Many structure.
Date Posted: 24 February 2009 at 3:41am

My contact http://www.tienphatjsc.vn/tienphat.htm" rel="no follow - 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.