MySQL merge table, with zero vaules

Question

Table A name is source

ID    |   date   |  valueS   | commonID
1       26.8.14     Svalue01   11
2       21.8.14     Svalue02   11
3       25.8.14     Svalue03   11

Table B name is destination

ID    |   date   |  valueD   | commonID
1       26.8.14     Dvalue01   11
2       21.8.14     Dvalue03   11
3       24.8.14     Dvalue03   11

So currently im using

SELECT a.*, b.* FROM (SELECT * FROM Source WHERE commonID = '11')a JOIN destination b ON a.commonID = b.commonID

But this dont get me the wished result.

i want something sorted by date, and if there is no record for both on the date, one is zero.

example how it should look

ID    |   date   |  valueD   | commonID | ID    |   date   |  valueS   | commonID
1       26.8.14     Dvalue01   11         1       26.8.14     Svalue01   11
                                          3       25.8.14     Svalue03   11
3       24.8.14     Dvalue03   11
2       21.8.14     Dvalue03   11         2       21.8.14     Svalue02   11

Is and how would this be possible?

Additional Info:

-Using Mysql 5.5.37 (MariaDB) -ID is primary on both -date fields are "timestamp" -value fields are INT -ID fields are INT -Engine is InnoDB

I hope i provided enough information and tried to make a good explained question

thank you for your help

What you want is a `FULL OUTER JOIN` -- which MySQL doesn't support. But there is [a workaround](http://stackoverflow.com/q/4796872/501250). — cdhowie, Aug 26 '14 at 15:57
please dont tell me thats how you are storing the dates .... — John Ruddell, Aug 26 '14 at 16:03
@JohnRuddell they are stored in timestamp format, this is just the display method and for example — sgt_johnny, Aug 26 '14 at 16:19
@hesamesa0r you should post the correct data. if you want to get faster help — John Ruddell, Aug 26 '14 at 16:21

score 0 · Answer 1 · answered Aug 26 '14 at 16:07

0

You need a Full outer Join

SELECT s.id, s.date, s.valueS, d.valueD, d.commonID FROM source s  LEFT JOIN destination d ON (s.id = d.id)
UNION
SELECT s.id, s.date, s.valueS, d.valueD, d.commonID  FROM source s  RIGHT JOIN destination d ON (s.id = d.id);

answered Aug 26 '14 at 16:07

Denis Kohl

739
8
13

but s.id = d.id... those ID's are not always the same? – sgt_johnny Aug 26 '14 at 16:21
this is incorrect on a few levels. you aren't doing a full outer join. – John Ruddell Aug 26 '14 at 16:23
@DenisKohl this isn't a full outer join in the respect of what the OP wants.. its the same thing as UNION'ing INNER JOINs. look at the data. and the joining column used – John Ruddell Aug 26 '14 at 16:36

John Ruddell · Accepted Answer · 2014-08-26T19:41:37.653

0

you want to join on the date as that is the determining column so something like this

SELECT 
    COALESCE(s.id, "") as s_id,
    COALESCE(s.date, "") as s_date,
    COALESCE(s.valueS, "") as 'valueS',
    COALESCE(s.commonID, "") as s_commonID,
    COALESCE(d.id, "") as d_id,
    COALESCE(d.date, "") as d_date,
    COALESCE(d.valueD, "") as 'valueD',
    COALESCE(d.commonID, "") as d_commonID
FROM source s
LEFT JOIN destination d on d.date = s.date
      AND d.commonID = s.commonID
WHERE d.commonID = 11

UNION

SELECT 
    COALESCE(s1.id, "") as s_id,
    COALESCE(s1.date, "") as s_date,
    COALESCE(s1.valueS, "") as 'valueS',
    COALESCE(s1.commonID, "") as s_commonID,
    COALESCE(d1.id, "") as d_id,
    COALESCE(d1.date, "") as d_date,
    COALESCE(d1.valueD, "") as 'valueD',
    COALESCE(d1.commonID, "") as d_commonID
FROM source s1
RIGHT JOIN destination d1 on d1.date = s1.date
       AND d1.commonID = s1.commonID
WHERE d1.commonID = 11
ORDER BY s_date DESC, d_date DESC

DEMO

edited Aug 26 '14 at 19:41

answered Aug 26 '14 at 16:10

John Ruddell

25,283
6
57
86

but i also need matching common ID ? what happends if there is an d.date like 26.8.14 with commonID 100 and a d.date like 26.8.14 with commonID... i think then it would merge? or am i wrong? – sgt_johnny Aug 26 '14 at 16:20
thank you! but can you take a look at the fiddle i modified? with differnt commonID, the commonID's should not be merged http://sqlfiddle.com/#!2/5d24f/1 – sgt_johnny Aug 26 '14 at 16:25
THANKS! but..i tried to add a WHERE.. why does it not work now? – sgt_johnny Aug 26 '14 at 16:29
it has be to d.commonID.. you can't reference an alias in the WHERE in the same select – John Ruddell Aug 26 '14 at 16:30
WHERE goes after JOIN and before ORDER BY let me edit my question – John Ruddell Aug 26 '14 at 16:33
Surely this cannot function as it's shown with single quotes in place of double quotes and vice versa. No? – mdahlman Aug 26 '14 at 20:10
@mdahlman its functioning on the sqlfiddle.. not sure what you mean.. I use COALESCE for null values and put in an empty string referenced with double quotes "" when there is a null value – John Ruddell Aug 26 '14 at 20:21
I stand corrected. MySQL seems fine with that. But empty strings and other literals should be in single quotes in all databases I can think of. And I believe identifiers must be in double quotes (or unquoted, of course) in all databases not named MySQL. – mdahlman Aug 27 '14 at 03:02

score 0 · Answer 3 · answered Aug 26 '14 at 19:43

I would go with a different solution for this problem. This starts by generating a cross product of all the common ids and dates that you want, and then using left join to bring in the other rows.

You only want one value for commonid, so this is slight overkill for your problem:

select s.*, dest.*
from (select 11 as commonid) c cross join
     (select date from source union
      select date from destination
     ) d left outer join
     source s
     on s.commonid = c.commonid and s.date = d.date left outer join
     destination dest
     on dest.commonid = c.commonid and dest.date = d.date;

But it is readily extendible. If you wanted two common ids, you could use:

select s.*, dest.*
from (select 11 as commonid union all select 12) c cross join
     (select date from source union
      select date from destination
     ) d left outer join
     source s
     on s.commonid = c.commonid and s.date = d.date left outer join
     destination dest
     on dest.commonid = c.commonid and dest.date = d.date;

This is a very good answer, this is exactly what i need. how can i name columns in this query, to avoid double fields like "date". — sgt_johnny, Aug 27 '14 at 05:22
@hesamesa0r . . . You have to list the columns explicitly and give them names. I just used `*` as a convenience. — Gordon Linoff, Aug 27 '14 at 11:41

MySQL merge table, with zero vaules

3 Answers3

Linked