CODEWITHSUNDEEP
mysqlfull-outer-join
Faztrick Fazil Pv
Faztrick Fazil Pv

Reputation: 264

How to join two tables in mysql

I have an income table that looks like this:

date              income      
---------------------------
09/05/13          56000    
09/05/13          66600
09/05/13          50000

And an expense table that looks like this:

date              expense 
----------------------------
09/05/13          68800

I want to write a query whose output looks like this:

date              income             expense 
---------------------------------------------
09/05/13          56000              68800
09/05/13          66600
09/05/13          50000

Upvotes: 0

Views: 147

Answers (1)

peterm
peterm

Reputation: 92845

UPDATE2: If you're not concerned with particular order in which income values are matched to expense values you can get your desired output with a query like this

SELECT date, 
       MAX(CASE WHEN type = 1 THEN amount END) income,
       MAX(CASE WHEN type = 2 THEN amount END) expense
  FROM
(
  SELECT 1 type, date, income amount, @n := IF(@g = date, @n + 1, 1) rnum, @g := date g
    FROM income CROSS JOIN (SELECT @n := 0, @g := NULL) i1
   UNION ALL
  SELECT 2, date, expense amount, @m := IF(@f = date, @m + 1, 1) rnum, @f := date g
    FROM expense CROSS JOIN (SELECT @m := 0, @f := NULL) i2
) q
 GROUP BY date, rnum

Output:

|               DATE | INCOME | EXPENSE |
|--------------------|--------|---------|
| September, 05 2013 |  56000 |   68800 |
| September, 05 2013 |  66600 |  (null) |
| September, 05 2013 |  50000 |  (null) |

Here is SQLFiddle demo

UPDATE1: It seems logical to group incomes and expenses per day with a query like

SELECT a.date, i.income, e.expense
  FROM
(
  SELECT date
    FROM income
  UNION
  SELECT date
    FROM expense 
) a LEFT JOIN 
(
  SELECT date, SUM(income) income
    FROM income
   GROUP BY date
) i
    ON a.date = i.date LEFT JOIN 
(
  SELECT date, SUM(expense) expense
    FROM expense
   GROUP BY date
) e
    ON a.date = e.date

Output:

|               DATE | INCOME | EXPENSE |
|--------------------|--------|---------|
| September, 05 2013 | 172600 |   68800 |

Here is SQLFiddle demo

Original answer to original question: If you need a FULL OUTER JOIN (over date column) as you stated in your original question then this will do it

SELECT a.date, i.income, e.expense
  FROM
(
  SELECT date
    FROM income
  UNION
  SELECT date
    FROM expense 
) a LEFT JOIN income i
    ON a.date = i.date LEFT JOIN expense e
    ON a.date = e.date

But then you will get this as output

|          DATE | INCOME | EXPENSE |
|---------------|--------|---------|
| September, 05 |  56000 |   68800 |
| September, 05 |  66600 |   68800 |
| September, 05 |  50000 |   68800 |

Here is SQLFiddle demo

Upvotes: 3

Related Questions