CODEWITHSUNDEEP
mysqljoinleft-joinmissing-data
Mc89
Mc89

Reputation: 11

Can't join two tables properly in mysql

i have these two tables

N-table                          M-table
-----------------------------    -----------------------------
| date       | id | n-value |    | date       | id | m-value |
|------------|----|---------|    |------------|----|---------|
| 2015-08-01 | 7  | 100     |    | 2015-09-01 | 7  | 200     |
| 2015-09-01 | 8  | 10      |    -----------------------------
-----------------------------

I'd like to join these two table like this

---------------------------------------------------
| date       | id | n-value       | m-value       |
-------------|----|---------------|---------------|
| 2015-08-01 | 7  | 100           | null ( or 0 ) |
| 2015-09-01 | 7  | null ( or 0 ) | 200           |
| 2015-09-01 | 8  | 10            | null ( or 0 ) |
---------------------------------------------------

I did this query:

  SELECT n.date , n.id , n.n-value, m.m-value FROM n
  LEFT JOIN m ON n.id = m.id AND n.date = m.date GROUP BY n.date

But the output isn't right:

---------------------------------------------------
| date       | id | n-value       | m-value       |
-------------|----|---------------|---------------|
| 2015-08-01 | 7  | 100           | null          |
| 2015-09-01 | 8  | 10            | null          |
---------------------------------------------------

What's wrong with my query?

Upvotes: 0

Views: 1395

Answers (2)

Jeff Watkins
Jeff Watkins

Reputation: 6359

If you were on an ANSI SQL RDBMS then you'd:

SELECT n.date , n.id, m.id , n.n-value, m.m-value FROM n
  FULL OUTER JOIN m ON n.id = m.id AND n.date = m.date GROUP BY n.date

This selects the results from both tables when there's no match.

Left outer join takes all items from the left and includes nulls where no match on the right and a right outer join does the opposite. Full includes both sides.

Note you'll also need to include m.id as for the middle row in your "expected" table, there is no n.id.

However, mySQL, as pointed out does not support a full outer join, so you could use (as mentioned in the link in the comment below)

SELECT n.date , n.id, n.n-value, m.m-value FROM n
  LEFT JOIN m ON n.id = m.id AND n.date = m.date GROUP BY n.date
UNION
SELECT m.date , m.id , n.n-value, m.m-value FROM n
  RIGHT JOIN m ON n.id = m.id AND n.date = m.date GROUP BY n.date

Upvotes: 1

wallabra
wallabra

Reputation: 442

You should tell the query to put the right table content in the top line, but organized the way you want it to.

Upvotes: 0

Related Questions