John Doe John Doe - 4 months ago 7
SQL Question

Three MySQL queries for one operation!? I think not

I'll try to be right to the point. Here's what I'm trying to do.
I have two tables that I need to pull data from:

membershipstable = || groupid || templateid || userid
|| 2 || 1 || 0
|| 0 || 1 || 3
|| 2 || 0 || 4

userstable = || id || firstname || lastname || email


What I would like to do would be something like:

SELECT * FROM userstable WHERE id = userid (in memberships table) -- AND/OR -- WHERE id = userid IF groupid is associated with templateid, and templateid is equal to $currenttemplateid


Or in human speak: Look at the
memberships
table and if you find a row containing a
templateid
that matches
$currenttemplateid
then bring back the
userid
from that row. Then, look at the
userstable
and retrieve the information for each user who has an
id
matching a
userid
we found above. Also, if you find any rows that contain a
templateid
that matches
$currenttemplateid
, then look at the
groupid
in that row and bring back any
userid
that is in any row with that
groupid
, and again retrieve the information for any user with an
id
that matches the
userid
that was found above.

I'm sorry if my explanation is somewhat confusing, and if my question reeks of newbishness (also, don't worry, I'm not using unprepared statements in the actual project)... I know that this can be done with MySQL in a single, efficient, and beautiful query but I'm at a complete loss as to what exactly that query would look like... Thank you very much for your help. :)

Answer

Use a 3-way join to find all the users in the same group as the selected member.

SELECT u.*
FROM usertable AS u
JOIN membershiptable AS m1 ON u.id = m1.userid
JOIN membershiptable AS m2 ON m1.groupid = m2.groupid
WHERE m2.templateid = $currenttemplateid

DEMO