MySql: merge three tables together

Question

I want to merge three tables together as shown here:

Basically I want to include the items from all three tables T1, T2 and T3 and have them merged as shown in the result table. I tried something like this:

SELECT T1.user, T2.tid, T2.name, T3.type, T1.mid
FROM T1
LEFT JOIN T2 ON T1.mid = T2.mid
LEFT JOIN T3 ON T2.tid = T3.tid
GROUP BY T1.user;

But it does not seem to have worked. It does show the results but only unique values. In the result if user is johny, it will only show the first value and ignore the second, though it should be in the result table.

Is there something I am missing?

Answer 1

The Group is not necessary if you want to see all results for each of the users. Otherwise it will hide some of the rows and show just one per user.

First join T1 Right to T2 than Left Join to T3. This is good practice if there is element from T1 that has no connection with element from T3 to prevent showing NULL result for T£ fields.

SELECT T1.user, T2.tid, T2.name, T3.type, T1.mid
FROM T1
RIGHT JOIN T2 ON T1.mid = T2.mid
LEFT JOIN T3 ON T2.tid = T3.tid;

Answer 2

Get rid of the "Group By" part. This should fix your problem.