php - Join 3 Tables with 3rd Table not associated to Table 2

Solution:

Remember a left join that has non-matching records will result in null data int3. I changed the last two lines. Try this:

SELECT t1.a, t1.b, t2.a, t2.b, t3.a, t3.b, t3.c 
FROM t1 LEFT JOIN t2 ON t1.b = t2.b 
LEFT JOIN t3 ON t2.b = t3.b 
WHERE t3.c = 'someuser' and t3.b is null
Copy code

Although, if you're doing a conditional check on t3, it's always going to be false (when not checking for null)...

Maybe you want this?

SELECT t1.a, t1.b, t2.a, t2.b, t3.a, t3.b, t3.c 
FROM t1 LEFT JOIN t2 ON t1.b = t2.b 
LEFT JOIN t3 ON t2.b = t3.b and t3.c = 'someuser'
WHERE t3.b is null
Copy code

The difference between the two queries is: the first one should always return 0 rows since you're expecting t3.b to be null (which MAY indicate that the joined table has no matching records) but then you check t3.c for a value which will never exist in the results. The second query filters on t3.c during the join, and then in your WHERE clause you find null matches, which should hopefully indicate that there was no match (depending on how your data is setup).

Solution:

SOLUTION:

SELECT t1.a, t1.b, t2.a, t2.b, t3.a, t3.b, t3.c 
FROM t1 LEFT JOIN t2 ON t1.b = t2.b 
LEFT JOIN t3 ON t2.b = t3.b and t3.c = 'someuser'
WHERE t3.b is null GROUP BY t3.c
Copy code