Re: Many to Many Query...

[Jay Pipes]

You can use a self join to accomplish this task:

SELECT m.*
FROM Members m
INNER JOIN ServiceMap sm1
ON m.MemberID = sm1.MemberID
INNER JOIN ServiceMap sm2
ON sm1.MemberID = sm2.MemberID
INNER JOIN ServiceMap sm3
ON sm2.MemberID = sm3.MemberID
WHERE sm1.ServiceID = 6
AND sm2.ServiceID = 11
AND sm3.ServiceID = 20;

Alternately, if you are using 4.1+ then you can use an EXISTS correlated subquery, repeated for each needed service:

SELECT m.*
FROM Members m
WHERE EXISTS (SELECT MemberID FROM ServiceMap WHERE MemberID = m.MemberID AND ServiceID = 6)
AND EXISTS (SELECT MemberID FROM ServiceMap WHERE MemberID = m.MemberID AND ServiceID = 11)
AND EXISTS (SELECT MemberID FROM ServiceMap WHERE MemberID = m.MemberID AND ServiceID = 20);

Alternately, again in 4.1+, you might find that this is quite a bit faster (though less flexible than the previous SQL statements):

SELECT m.*
FROM Members m
INNER JOIN (
SELECT MemberID, COUNT(*)
FROM ServiceMap
WHERE ServiceID IN (6,11,20)
GROUP BY ServiceID
HAVING COUNT(*) = 3
) as sm
ON m.MemberID = sm.MemberID;

Additionally, I would recommend eliminating the AUTO_INCREMENT primary key in the ServiceMap table, as it is not needed at all, and making the PRIMARY KEY on (MemberID, ServiceID). Depending on how much write activity there is on ServiceMap, and which of the above query styles you choose to use, a UNIQUE INDEX on (ServiceID, MemberID) might come in very handy, even though it is somewhat redundant.

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com