MySQL: Query from multiple tables

matdlara 0
Viewed 33

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.

mysql database
2 Answers

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
Thorsten Kettner0

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

matdlara0
Related