How To Join Multiple Tables In Sql

Hi friends I have 3 table topups, withdraws and transfers these 3 tables belongs to the user table. I have to find all the records which belongs to the users. I tried with inner jo

Solution 1:

Left joining them to the users and to a number works for this.

SELECT 
 tup.amount, tup.result, 
 usr.id as user_id, 
 wd.w_amount, 
 trans.method
FROM users usr
CROSS JOIN (SELECT generate_series n FROM generate_series(1, 3)) AS nr
LEFT JOIN topups tup ON tup.user_id = usr.id AND nr.n = 1
LEFT JOIN withdraws wd ON wd.user_id = usr.id AND nr.n = 2
LEFT JOIN transfers trans ON trans.user_id = usr.id AND nr.n = 3WHERE (tup.user_id ISNOT NULL OR wd.user_id ISNOT NULL OR trans.user_id ISNOT NULL)
ORDERBY tup.user_id, wd.user_id, trans.user_id

Test it here

Extra:

A variation based on the comments here

Solution 2:

You can use left joins instead of inner joins:

SELECT*FROM users u
LEFTJOIN topups t
  ON u.id = t.user_id
LEFTJOIN withdraws w
  ON u.id = w.user_id
LEFTJOIN transfers tf
  ON u.id = tf.user_id