Reputation: 1272
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
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