John Doe John Doe - 1 year ago 40
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
table and if you find a row containing a
that matches
then bring back the
from that row. Then, look at the
and retrieve the information for each user who has an
matching a
we found above. Also, if you find any rows that contain a
that matches
, then look at the
in that row and bring back any
that is in any row with that
, and again retrieve the information for any user with an
that matches the
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 Source

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

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