How to join multiple tables in sql

Question

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 join as follows:-

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

But this query returns only the common records between the 3 tables. i have to find all those records which belongs to the user for each table.

Suppose i have 2 records in topups which belongs to user id 1, 3 records in withdraws which belongs to user id 2 and 5 records in transfers which belongs to user id 3 so i should get the total 10 records.

sample data:-

topups

+--------+---------+---------+
| amount | result  | user_id |
+--------+---------+---------+
|     10 | success |       1 |
|     20 | failed  |       2 |
+--------+---------+---------+

withdraws

+---------+----------+
|w_amount |  user_id |
+---------+----------+
|     10  |        1 |
|     20  |        2 |
|     30  |       10 |
+---------+----------+

Transfers

+--------+--------+---------+
| method | amount | user_id |
+--------+--------+---------+
| abc    |     10 |       3 |
| xyz    |     20 |       4 |
+--------+--------+---------+

users

+----+---------+--------+
| id |  f_name | l_name |
+----+---------+--------+
|  1 |    abc  |    xyz |
|  2 |    abc  |    xyz |
|  3 |    abc  |    xyz |
|  4 |    abc  |    xyz |
|  5 |    abc  |    xyz |
|  6 |    abc  |    xyz |
+----+---------+--------+

Expected output

+--------+---------+---------+----------+---------+
| amount | result  | user_id | w_amount |  method |
+--------+---------+---------+----------+---------+
|     10 | success |       1 |          |         |
|     20 | failed  |       2 |          |         |
|        |         |       1 |       10 |         |
|        |         |       2 |       20 |         |
|        |         |       3 |          | abc     |
|        |         |       4 |          | xyz     |
+--------+---------+---------+----------+---------+

Please help Thanks in advance.

Instead of using INNER JOIN, you need to use LEFT/RIGHT OUTER JOIN. [You can start by reading this](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join?rq=1) — Luc M, Dec 15 '18 at 14:24
To return exactly 10 records from those 3 tables then it seems you could use [UNION ALL](https://www.w3schools.com/sql/sql_union.asp). But that's only usefull for output of the same type of fields. — LukStorms, Dec 15 '18 at 14:33
explaiing 10 records you want by saying wont do sample output is required!! — Nikhil S, Dec 15 '18 at 14:43
I removed the incompatible database tags. Please tag only with the database you are really using. — Gordon Linoff, Dec 15 '18 at 14:43
@GordonLinoff i have already provided the example. user table has many users but only some of the users has topups, withdraws and transfer i have to find all those records from the all 3 tables which belongs to the user — awsm sid, Dec 15 '18 at 14:53
Please **[EDIT]** your question and add some [sample data](https://ozh.github.io/ascii-tables/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. — , Dec 15 '18 at 15:05
@awsmsid . . . Your question is unclear. There are multiple attempts to answer it, and you seem to reject all of them. Without sample data, desired results, and a good explanation, there is no way to answer. Strangers cannot read your mind. — Gordon Linoff, Dec 15 '18 at 15:42
Looks like a broken database to me. How can a withdraws record for user 10 exist, when there is no user with that ID? That doesn't seem to make sense and should be made impossible by applying an appropriate foreign key. — Thorsten Kettner, Dec 15 '18 at 17:42
Please read & act on [mcve]. That includes clearly explaining how you get an answer no matter what the input is. Use enough steps, words, sentences & references to parts of examples. — philipxy, Dec 15 '18 at 18:53
@ThorstenKettner why a record for user 10 can't exist? what about if i created a withdraw record for user 10 and deleted that user from user table? one more scenerio can be here suppose withdraw is present without user_id. Please don't downvote for the question its a valid question i have the same scenerio thats why i posted this question — awsm sid, Dec 16 '18 at 07:39
@ThorstenKettner for your kind of information i got the solution please check below LukStorms answer :) — awsm sid, Dec 16 '18 at 08:01
@awsm sid: I didn't downvote. But in a properly designed database you couldn't delete a user when there is still data for that user. You also couldn't create withdraw data for a user that doesn't exist. There would be a foreign key on `withdraws.user_id` to `users.id`. — Thorsten Kettner, Dec 16 '18 at 14:07

LukStorms · Answer 1 · 2018-12-16T12:41:22.643

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 = 3
WHERE (tup.user_id IS NOT NULL OR wd.user_id IS NOT NULL OR trans.user_id IS NOT NULL)
ORDER BY tup.user_id, wd.user_id, trans.user_id

Test it here

Extra:

A variation based on the comments here

edited Dec 16 '18 at 12:41

answered Dec 15 '18 at 17:34

LukStorms

28,916
5
31
45

1

can i combine the value of two columns into one. suppose topups and withdraws belons to user id 1 and both have update_at so i want to combine the value of updated_at like CONCAT(tup.updated_at, ',', wd.updated_at) AS updated_at – awsm sid Dec 16 '18 at 08:57
but it its not concating the value each time i am getting a single value for updated_at like "2018-10-15 12:16:09.799762,," or ",,2018-10-29 19:06:13.814336" i am trying to concat the updated_at of 3 tables – awsm sid Dec 16 '18 at 09:13
I would probably go for a `CONCAT_WS(',', tup.updated_at, wd.updated_at)`. But you wanted the tables separated, so those 2 values are on different lines. – LukStorms Dec 16 '18 at 09:14
A `COALESCE(tup.updated_at, wd.updated_at, trans.updated_at)` would work in this setup, if you want only 1 "updated_at". – LukStorms Dec 16 '18 at 09:20
@awsmsid The problem with this is that each table has multiple records per user_id. And to avoid a cartesian product join (and generate to many lines) you'd need to join on something extra. Check out this [test](https://rextester.com/NVHS79498). Basically, it joins on sub-queries that group on the user_id and the amount. That's the closest I could get to what you tried to do. – LukStorms Dec 16 '18 at 12:39

score 0 · Answer 2 · answered Dec 15 '18 at 14:28

0

You can use left joins instead of inner joins:

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

answered Dec 15 '18 at 14:28

Mureinik

297,002
52
306
350

i tried but its not working. it returns all those records which do not belongs to users – awsm sid Dec 15 '18 at 14:50

How to join multiple tables in sql

2 Answers2

Linked