Merge 2 recordset by view MySql

Question

I have two recordset, the first:

ID Field1 Field2  Field3  Field4  
 1   aa1    bb1    cc1     NULL
 2   aa2    bb2    cc2     NULL

The second one:

ID Field1 Field2  Field3   Field4  
 1   aa1    bb1    NULL     dd1
 2   aa2    bb2    NULL     dd2

I would like to create a view that merges the two so that with the same key are grouped in the same line, i would like to get:

ID Field1 Field2  Field3   Field4  
 1   aa1    bb1    cc1      dd1
 2   aa2    bb2    cc2      dd2

I tried this code but did not get the result hoped:

SELECT * FROM (
SELECT
    t.ID,
    t.Field1,
    t.Field2,
    t.Field3,
    NULL as 'Field4'
FROM TABLE
WHERE ...

UNION ALL

SELECT
    t.ID,
    t.Field1,
    t.Field2,
    NULL as 'Field3',
    t.Field4
FROM TABLE
WHERE ...

) GROUP BY ID, Field1, Field2

You need a join, not a union. Join is horizontal, union is vertical. — Shadow, Nov 09 '17 at 09:37

score 0 · Answer 1 · answered Nov 09 '17 at 09:40

Try this answer, I hope this helps you:

SELECT COALESCE(T1.ID,T2.ID)ID
    ,COALESCE(T1.Field1,T2.Field1)Field1
    ,COALESCE(T1.Field2,T2.Field2)Field2
    ,COALESCE(T1.Field3,T2.Field3)Field3
    ,COALESCE(T1.Field4,T2.Field4)Field4
from Table1 T1
INNER JOIN Table2 T2 ON T1.ID=T2.ID

Merge 2 recordset by view MySql

1 Answers1