John Doe John Doe - 4 months ago 10
SQL Question

Inner Join MySQL Query Disregards 'WHERE' Clause?

I'm having a bit of trouble with a somewhat complex query, and I can't figure out what exactly is going wrong...

The query deals with two MySQL tables:

$userstable:
|| id || firstname || lastname
|| 1 || John || Doe

$membershipstable:
|| groupid || templateid || userid ||
|| 0 || 1 || 1 ||
|| 1 || 0 || 2 ||
|| 0 || 2 || 3 ||


Here is the query:

$query = "
SELECT DISTINCT
u.id,
u.firstname,
u.lastname
FROM $userstable AS u
INNER JOIN $membershipstable AS M1 ON u.id = M1.userid
INNER JOIN $membershipstable AS M2 ON M1.groupid = M2.groupid
WHERE M2.templateid = :currenttemplateid
";


For reference, I have already double checked the variable for the placeholder
:currenttemplateid
and it returns correctly... What I am trying to do is return from the query only records for the current template's
id
, however the query returns the user data from the
$userstable
for any user associated with a template regardless of whether that template's
id
matches the current one specified in the
WHERE
clause.

If I've just completely newbd out on this one then I apologize, but I've been working with this code for a few hours now so my senses may not be as sharp as they should be... Any help is very much appreciated.

Answer

Modify Your Query As follows:-

$query = " SELECT DISTINCT u.id, u.firstname, u.lastname 
           FROM $userstable AS u 
           INNER JOIN $membershipstable AS M1 ON u.id = M1.userid 
           INNER JOIN $membershipstable AS M2 ON M1.groupid = M2.groupid and M2.templateid = :currenttemplateid ";