CODEWITHSUNDEEP
mysql
Manish
Manish

Reputation: 699

How to create a master table in mysql

I have 6 tables(A,B,C,D,E,F), all with the following list of columns:

Date Price City Country Pin

Lets say table A has few rows like this:

Date       Price City Country Pin
19-01-2019 200   Pune India   411010

Lets say table B has few rows like this:

Date       Price City   Country Pin
19-01-2019 300   Mumbai India   411010

Lets say table C has few rows like this:

Date       Price City     Country Pin
19-01-2019 50    Kolhapur India   411010

Lets say table D has few rows like this:

Date       Price City   Country Pin
19-01-2019 200   Satara India   411010

Lets say table E has few rows like this:

Date       Price City     Country Pin
19-01-2019 20    Khandala India   411010

Lets say table F has few rows like this:

Date       Price City     Country Pin
19-01-2019 10    Lonavala India   411010

I want to create a table, say master table containing Date & Price from all these tables.Name of the column price should be what is the name of the table.

Expected Output:

Date       A   B   C  D   E   F
19-01-201  200 300 50 200 20  10

how can this be achieved?

Upvotes: 0

Views: 2841

Answers (1)

trincot
trincot

Reputation: 350951

You could use union all to combine all tables, reserving a column for each:

select date, sum(a) a, sum(b) b, sum(c) c, sum(d) d, sum(e) e
from (
        select date, price a, null b, null c, null d, null e
        from a
        union all 
        select date, null, price, null, null, null
        from b
        union all 
        select date, null, null, price, null, null
        from c
        union all 
        select date, null, null, null, price, null
        from d
        union all 
        select date, null, null, null, null, price
        from e) as x
group by date;

If you are happy with this result, then you can create a table for it by preceding the previous select query with:

create table master(Date date, A int, B int, C int, D int, E int) as

Upvotes: 1

Related Questions