MySQL: Joining four tables based on user

Question

After spending few hours yesterday, I decided to post here. I have a database with few tables and I need to join 4 tables based on users table's idusers (id of user).

Four tables that needs to be joined are: users, table1, table2 and table3.

users
idusers, name, email, a, b, c, etc.

table1 
custom_id1, a_id, users_idusers

table2 
custom_id2, a_id, users_idusers, comment

table3 
custom_id3, lang

How they are related: - User signs up and the data is stored to users table: I need all fields from this table. - When they take an action, it gets stored in table1 (where users_idusers is users.idusers). - Some actions (with comment) register to table2 (users_idusers is users.idusers). - table1 and table2's a_id field is same and mapped to table3's custom_id3.

What I need: I want to be able to get all actions from table1 mapped to its user from the user table, table3 for lang and table2 (if there is a comment, get the text or return null for that field.).

What my query looks like:

SELECT table1.a_id, table1.users_idusers, users.*, table3.langkey
    FROM table1
    LEFT JOIN users ON (users.idusers = table1.users_idusers)
    LEFT JOIN table3 ON (table1.a_id = table3.custom_id3);

The query above is missing the part that needs to join table2 and that's where I'm stuck. Any help would be appreciated.

Thank you.

Can't you join on table2 the same way you joined table1 to users? — Patrick Q
@PatrickQ Assuming you meant table3 instead of table1. That's what I kept trying to do for table2 yesterday. For some reason my 15K results becomes 700K. So no, it doesn't work by doing another join. — ialphan

Strawberry Strawberry · Accepted Answer · 2014-05-16T17:36:11

SELECT u.*
     , and
     , some
     , other
     , columns
  FROM users u
  JOIN table1 a
    ON a.users_idusers = u.idusers
  JOIN table3 l
    ON l.custom_id3 = a.a_id
  LEFT
  JOIN table2 c
    ON c.a_id = a.a_id
  [AND c.users_idusers = a.users_idusers]??

MySQL: Joining four tables based on user

1 Answers