I need help with the JOIN request in mysql

advertisements

I have started learning MySQL and I'm having a problem with JOIN.

I have two tables: purchase and sales

purchase
--------------
p_id  date          p_cost   p_quantity
---------------------------------------
1     2014-03-21       100       5
2     2014-03-21        20       2

sales
--------------
s_id  date          s_cost   s_quantity
---------------------------------------
1     2014-03-21       90       9
2     2014-03-22       20       2

I want these two tables to be joined where purchase.date=sales.date to get one of the following results:

Option 1:

p_id  date         p_cost  p_quantity   s_id   date        s_cost   s_quantity
------------------------------------------------------------------------------
1     2014-03-21    100       5         1      2014-03-21   90       9
2     2014-03-21     20       2         NULL   NULL         NULL     NULL
NULL  NULL         NULL      NULL       2      2014-03-22   20       2

Option 2:

p_id  date         p_cost  p_quantity   s_id   date        s_cost   s_quantity
------------------------------------------------------------------------------
1     2014-03-21    100       5         NULL   NULL         NULL     NULL
2     2014-03-21     20       2         1      2014-03-21   90       9
NULL  NULL         NULL      NULL       2      2014-03-22   20       2

the main problem lies in the 2nd row of the first result. I don't want the values
2014-03-21, 90, 9 again in row 2... I want NULL instead.

I don't know whether it is possible to do this. It would be kind enough if anyone helps me out.

I tried using left join

SELECT * FROM sales LEFT JOIN purchase ON sales.date = purchase.date

output:

s_id date s_cost s_quantity p_id date p_cost p_quantity 1 2014-03-21 90 9 1 2014-03-21 100 5 1 2014-03-21 90 9 2 2014-03-21 20 2 2 2014-03-22 20 2 NULL NULL NULL NULL

but I want 1st 4 values of 2nd row to be NULL


Since there are no common table expressions or full outer joins to work with, the query will have some duplication and instead need to use a left join unioned with a right join;

SELECT p_id, p.date p_date, p_cost, p_quantity,
       s_id, s.date s_date, s_cost, s_quantity
FROM (
  SELECT *,(SELECT COUNT(*) FROM purchase p1
            WHERE p1.date=p.date AND p1.p_id<p.p_id) rn FROM purchase p
) p LEFT JOIN (
  SELECT *,(SELECT COUNT(*) FROM sales s1
            WHERE s1.date=s.date AND s1.s_id<s.s_id) rn FROM sales s
) s
ON s.date=p.date AND s.rn=p.rn

UNION

SELECT p_id, p.date p_date, p_cost, p_quantity,
       s_id, s.date s_date, s_cost, s_quantity
FROM (
  SELECT *,(SELECT COUNT(*) FROM purchase p1
            WHERE p1.date=p.date AND p1.p_id<p.p_id) rn FROM purchase p
) p RIGHT JOIN (
  SELECT *,(SELECT COUNT(*) FROM sales s1
            WHERE s1.date=s.date AND s1.s_id<s.s_id) rn FROM sales s
) s
ON s.date=p.date AND s.rn=p.rn

An SQLfiddle to test with.