Can't join two tables properly in mysql

Question

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?

If you are using a - (minus) in the column name, then you will need to back-quote the names in the query : m.`m-value` otherwise it will be treated as an expression "m.m - value" — PaulF, Sep 07 '15 at 16:23

Jeff Watkins · Accepted Answer · 2015-09-07T16:19:32.623

1

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

edited Sep 07 '15 at 16:19

answered Sep 07 '15 at 15:56

Jeff Watkins

6,343
16
19

MySQL does not support full outer join : http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – PaulF Sep 07 '15 at 16:06
Thank you! Hovewer can you take only those records with ( for example ) the id 7 with one where clause only? Or I have to write the where clause in both selects? I'm trying to avoid this action. – Mc89 Sep 07 '15 at 16:16
unfortunately, you're doing a union, it's effectively two SQL queries with matching columnar output. I've edited my SQL to only include the "Id" from the side that's being outer joined. – Jeff Watkins Sep 07 '15 at 16:17
1

Actually i did this query for my purpose and it worked: `SELECT * FROM ( SELECT n.date , n.id, n.nvalue, m.mvalue 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.nvalue, m.mvalue FROM n RIGHT JOIN m ON n.id = m.id AND n.date = m.date GROUP BY n.date ) AS t1 WHERE t1.id = (idvariable)` – Mc89 Sep 08 '15 at 07:48

score 0 · Answer 2 · answered Sep 07 '15 at 15:59

0

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

answered Sep 07 '15 at 15:59

wallabra

412
8
17

The thing is i have to do a query that can be executed in both tables, despite their record length. In this case i have 2 records vs 1 record, but in the future there will be 4 vs 6, for example. – Mc89 Sep 07 '15 at 16:13
@Mc89 I have a nice logistics, but unfortunately I don't understand MySQL. Put each table a ID so they can be called referencing their ID, then create a 3rd table which is a union of the other two, and add the third one a unique ID. Make a loop called every minute or so which updates the third table's content relative to the others. – wallabra Sep 13 '15 at 13:35

Can't join two tables properly in mysql

2 Answers2