CODEWITHSUNDEEP
mysqlsql
Zedd
Zedd

Reputation: 204

MySQL three table query or union

I have 3 tables, they have a common field user_id

gain_table

+--------------------------+
|user_id    |   gain_count |
| 1         |   3          |
| 2         |   4          |
| 3         |   1          |
+--------------------------+

consume_table

+--------------------------+
|user_id    |consume_count |
| 2         |   5          |
| 5         |   4          |
| 6         |   7          |
+--------------------------+

join_table

+--------------------------+
|user_id    |   join_count |
| 1         |   3          |
| 2         |   1          |
| 5         |   4          |
+--------------------------+

I want get output like this:

Output:

+-----------+--------------+--------------+------------+
|user_id    |   gain_count |consume_count | join_count |
| 1         |   3          |     0        | 3          |
| 2         |   4          |     5        | 1          |
| 3         |   1          |     0        | 0          |
| 5         |   0          |     4        | 4          |
| 6         |   0          |     7        | 0          |
+-----------+--------------+--------------+------------+

Yes, I want these three table to union as one table, and if some field value is empty then give 0 to this field value.

How to write MySQL query?

Upvotes: 0

Views: 42

Answers (2)

Nick
Nick

Reputation: 147176

You can get the result you want with a UNION of all the tables, selecting 0 for non-existent values in each table and then summing all fields by user_id:

SELECT user_id, SUM(gain_count) AS gain_count, 
    SUM(consume_count) AS consume_count, SUM(join_count) AS join_count
FROM (SELECT user_id, gain_count, 0 AS consume_count, 0 AS join_count FROM gain_table
      UNION ALL
      SELECT user_id, 0, consume_count, 0 FROM consume_table
      UNION ALL
      SELECT user_id, 0, 0, join_count FROM join_table) u
GROUP BY user_id

Output:

user_id     gain_count  consume_count   join_count
1           3           0               3
2           4           5               1
3           1           0               0
5           0           4               4
6           0           7               0

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415840

Here's how to do it with ansi-standard sql:

SELECT coalesce(g.user_id, c.user_id, j.user_id) user_id
    , coalesce(g.gain_count, 0) gain_count
    , coalesce(c.consume_count, 0) consume_count
    , coalesce(j.join_count,0) join_count
FROM gain_table g
FULL JOIN consume_table c on c.user_id = g.user_id
FULL JOIN join_table j on j.user_id = coalsece(c.user_id, g.user_id)

This has been part of the ansi standard since 92 release, and every major database but MySql can do it. MySql has to do this instead:

SELECT ids.user_id
    , coalesce(g.gain_count, 0) gain_count
    , coalesce(c.consume_count, 0) consume_count
    , coalesce(j.join_count, 0) join_count
FROM (
    SELECT user_id FROM gain_table
    UNION
    SELECT user_id FROM consume_table
    UNION
    SELECT user_id FROM join_table
) ids
LEFT JOIN gain_table g on g.user_id = ids.user_id
LEFT JOIN consume_table c on c.user_id = ids.user_id
LEFT JOIN join_table j on j.user_id = ids.user_id

Upvotes: 0

Related Questions