CODEWITHSUNDEEP
mysql
Ranadeep
Ranadeep

Reputation: 153

MYSQL UNION ALL with UNIQUE Rows

I have two tables as follows:

Table1

id           date            pprice
 1           22-1-2013       54.56
 2           15-2-2013       55.64 
 3           25-3-2013       57.62

and

Table2

id           date            dprice
1            12-1-2013       66.56
2            17-2-2013       68.59
3            25-3-2013       70.25

Now, I am using the following query

SELECT * FROM (
(SELECT Table1.date, Table1.pprice AS P_Price, NULL AS D_Price FROM Table1)
UNION ALL
(SELECT Table2.date, NULL AS P_Price, Table2.dprice AS D_Price FROM Table2)
) results
ORDER BY date DESC

This results in the following output:

date           P_Price          D_Price
25-3-2013      NULL             70.25
25-3-2013      57.62            NULL
17-2-2013      NULL             68.59
15-2-2013      55.64            NULL
22-1-2013      54.56            NULL
12-1-2013      NULL             66.56

In this case the date 25-3-2013 is shown twice to display the P_Price once and the D_Price once. I am trying to get the date row to show up only once with both the prices entered and no NULL like 

  date           P_Price          D_Price
**25-3-2013      57.62            70.25**
  17-2-2013      NULL             68.59
  15-2-2013      55.64            NULL
  22-1-2013      54.56            NULL
  12-1-2013      NULL             66.56

How can I get this?

Upvotes: 1

Views: 71

Answers (2)

sgeddes
sgeddes

Reputation: 62831

Unfortunately MySQL doesn't support the FULL OUTER JOIN, but you can achieve the same result as follows:

select t1.date, t1.pprice AS P_Price, t2.dprice AS D_Price 
from Table1 t1
    left join table2 t2 on t1.date = t2.date
union  
select t2.date, t1.pprice AS P_Price, t2.dprice AS D_Price 
from Table2 t2
    left join table1 t1 on t2.date = t1.date

Upvotes: 3

user359040
user359040

Reputation:

Use SUM and GROUP BY:

SELECT date, SUM(P_Price) AS P_Price, SUM(D_Price) AS D_Price 
FROM (SELECT Table1.date, Table1.pprice AS P_Price, NULL AS D_Price FROM Table1
      UNION ALL
      SELECT Table2.date, NULL AS P_Price, Table2.dprice AS D_Price FROM Table2
     ) results
GROUP BY date
ORDER BY date DESC

Upvotes: 1

Related Questions