CODEWITHSUNDEEP
mysqlsql
Mehedi Hasan Siam
Mehedi Hasan Siam

Reputation: 1272

Combine two tables with different date value according to their data

I'm working on project to display amount information in a website. And I want to ask how to combine two tables in SQL and it's values.

I am giving sample table

Table 1

id           date     amount1
-----------------------------
1         2021-02-24     100
2         2021-02-25     200
3         2021-02-26     300
4         2021-02-26     400

Table 2

id           date     amount
-----------------------------
1         2021-02-24     500
2         2021-02-25     600
3         2021-02-25     700

I want To Display data something like that

  date          amount1        amount
----------------------------------------
2021-02-24       100            500
2021-02-25       200            600
2021-02-25        0             700
2021-02-26       300            0
2021-02-26       400            0

But I was Unable to show the data something like that.

My sql Query was

SELECT 
       a.date,
       a.amount1,
       a.amount
FROM (
SELECT 
       t1.date,
       t1.amount1,
       t2.amount
FROM table2 t2
LEFT JOIN table1 t1 ON t1.date = t2.date
UNION
SELECT 
       t1.date,
       t1.amount1,
       t2.amount
FROM table2 t2
RIGHT JOIN table1 t1 ON t1.date = t2.date ) a

The out put was

  date          amount1        amount
----------------------------------------
2021-02-24       100            500
2021-02-25       200            600
2021-02-25       200            700
2021-02-26       300            0
2021-02-26       400            0

In the third row amount1 should be 0. But it's showing a data.

Actually, what I want. I want to fetch the dates from two tables in a single column. And the amounts from two tables. When there is no data according to date, that column will be Null or zero. How can I do that?

Upvotes: 0

Views: 398

Answers (1)

Akina
Akina

Reputation: 42854

SELECT dates.`date`, 
       COALESCE(data21.amount1, 0) amount1, 
       COALESCE(data22.amount2, 0) amount2
FROM ( SELECT `date`, row_number11 row_number
       FROM ( SELECT table1.*, 
                     @row_number11 := CASE WHEN @date11 = table1.`date`
                                           THEN @row_number11 + 1
                                           ELSE 1 END row_number11,
                     @date11 := table1.`date` date1
              FROM table1
              CROSS JOIN ( SELECT @date11:=NULL, @row_number11:= 0 ) variables
              ORDER BY table1.`date`, table1.id
            ) data11
       UNION 
       SELECT `date`, row_number12 row_number
       FROM ( SELECT table2.*, 
                     @row_number12 := CASE WHEN @date12 = table2.`date`
                                           THEN @row_number12 + 1
                                           ELSE 1 END row_number12,
                     @date12 := table2.`date` date2
              FROM table2
              CROSS JOIN ( SELECT @date12:=NULL, @row_number12:= 0 ) variables
              ORDER BY table2.`date`, table2.id
            ) data12
       ) dates
LEFT JOIN ( SELECT table1.*, 
                   @row_number21 := CASE WHEN @date21 = table1.`date`
                                    THEN @row_number21 + 1
                                    ELSE 1 END row_number21,
                   @date21 := table1.`date` date1
            FROM table1
            CROSS JOIN ( SELECT @date21:=NULL, @row_number21:= 0 ) variables
            ORDER BY table1.`date`, table1.id
          ) data21 ON dates.`date` = data21.`date` 
                  AND dates.row_number = data21.row_number21
LEFT JOIN ( SELECT table2.*, 
                   @row_number22 := CASE WHEN @date22 = table2.`date`
                                    THEN @row_number22 + 1
                                    ELSE 1 END row_number22,
                   @date22 := table2.`date` date2
            FROM table2
            CROSS JOIN ( SELECT @date22:=NULL, @row_number22:= 0 ) variables
            ORDER BY table2.`date`, table2.id
          ) data22 ON dates.`date` = data22.`date` 
                  AND dates.row_number = data22.row_number22
ORDER BY `date`

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=d416718749843f0f8b8973855cbb93ed

PS. I cannot see the way to do this more effective / less complex on your ancient MySQL version. I strongly recommend you to upgrade MySQL to the actual version.

Upvotes: 1

Related Questions