MySQL: Query from multiple tables

Question

I have a database MySQL 8.0 with 12 tables (one of each month) with information about clients. All tables have a Client ID and the amount of money that they save. For example, for two months:

+------------------+
+      January     +
+------------------+
+ ClienID | Amount + 
+------------------+
+ qwer23  | 23     +

+------------------+
+      December     +
+------------------+
+ ClienID | Amount + 
+------------------+
+ qwer23  | 15     +

And I want to get a table with the ClientID and the Amount of each month. Like this

+------------------+----------------+----------+
+ ClienID | January|                | December |
+------------------+----------------+----------+
+ qwer23  | 23     |                |     15   |

I search some options, but I'm not sure how to use JOIN or GROUP+BY.

Seeing a client ID in the tables, I assume there is also a client table? As to the question: you don't need GROUP BY, because there is nothing to aggregate. The tables already contain the amounts you want to show. It is just about joins. Full outer joins would be perfect, by MySQL does not support them. So, select from the clients table, then outer join the months. — Thorsten Kettner
– Thorsten Kettner, Commented Sep 19, 2022 at 17:04

Thorsten Kettner · Accepted Answer · 2022-09-19 19:16:43Z

You have one table for each of the twelve months, and a client can have up to one row in each table. To get a result row per client with all their monthly amounts, you'd full outer join all tables on the client ID. MySQL, however, still doesn't support full outer joins.

As the tables have a client ID, I assume there is also a client table these IDs are linking to. So, just select the clients from the clients table and outer join the months:

select
  clientid,
  january.amount as january,
  february.amount as february,
  ...
  december.amount as december
from client
left outer join january using (clientid)
left outer join february using (clientid)
...
left outer join december using (clientid);

If you don't have a client table (which seems unlikely), you can create one on-the-fly, by replacing

from client

by

from
(
  select clientid from january  union
  select clientid from february union
  ...
  select clientid from december
) client

matdlara · Accepted Answer · 2022-09-20 18:46:06Z

Thanks for all, I solve my problem with the following code:

SELECT 
    enero.ClienID, 
    enero.Amount as Enero,
    febrero.Amount as Febrero,
    marzo.Amount as Marzo,
    abril.Amount as Abril,
    mayo.Amount as Mayo,
    junio.Amount as Junio,
    julio.Amount as Julio,
    agosto.Amount as Agosto,
    septiembre.Amount as Septiembre,
    octubre.Amount as Octubre,
    noviembre.Amount as Noviembre,
    diciembre.Amount as Diciembre
FROM enero
    INNER JOIN febrero ON febrero.ClienID = enero.ClienID
    INNER JOIN marzo ON marzo.ClienID = enero.ClienID
    INNER JOIN abril ON abril.ClienID = enero.ClienID
    INNER JOIN mayo ON mayo.ClienID = enero.ClienID
    INNER JOIN junio ON junio.ClienID = enero.ClienID
    INNER JOIN julio ON julio.ClienID = enero.ClienID
    INNER JOIN agosto ON agosto.ClienID = enero.ClienID
    INNER JOIN septiembre ON septiembre.ClienID = enero.ClienID
    INNER JOIN octubre ON octubre.ClienID = enero.ClienID
    INNER JOIN noviembre ON noviembre.ClienID = enero.ClienID
    INNER JOIN diciembre ON diciembre.ClienID = enero.ClienID
WHERE enero.ClienID IN ('qwer23')

Note: The original table is in spanish

Collectives™ on Stack Overflow

MySQL: Query from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related