0

I have multiple tables and made 2 sub-selects (UserRecord,CustomerRecord) that i would like to merge into 1 table

UserRecord
========================
| RecordID | UserName |
========================
| 1        | Sara     |
| 1        | Tom      |
| 2        | Sara     |
| 2        | Kurt     |
| 3        | Fre      |
========================

Table: CustomerRecord
============================
| RecordID | CustomerName |
============================
| 1        | Jef          |
| 2        | Alex         |
| 2        | Peter        |
============================

Table: This should be the result
=======================================
| RecordID | UserName | CustomerName | 
=======================================
| 1        | Sara     | -            |
| 1        | Tom      | -            |
| 1        | -        | Jef          |
| 2        | Sara     | -            |
| 2        | Kurt     | -            |
| 2        | -        | Alex         |
| 2        | -        | Peter        |
| 3        | Fre      | -            |
=======================================

- = null

I tried with left, right, left outer, right outer ... join on the 2 tables but i don't get what i would like.

SELECT *
FROM UserRecord AS ur
INNER JOIN CustomerRecord AS cr ON ur.RecordID = cr.RecordID;
Sam_vdd
  • 686
  • 1
  • 6
  • 19
  • If there's no relationship between the tables (as your result seems to show) I don't think a join is the answer. Try using a union between selecting from the two tables, with NULL as the column that doesn't exist. – George T Apr 24 '14 at 08:52
  • Use a (full) `OUTER JOIN`. In mysql, that might mean to `UNION` a `RIGHT OUTER JOIN` with a `LEFT OUTER JOIN`. For reference, see this: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – germi Apr 24 '14 at 08:52
  • Mysql Workbench won't accept full outer join or just outer join – Sam_vdd Apr 24 '14 at 08:53

2 Answers2

0

You can use a simple union 

select recordid, username, null as customername from userrecord
union
select recordid, null, customername from customerrecord
Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198
0

What you want is not a join, but a UNION:

SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
UNION
SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord

... which just appends records from the two tables.

I'd just add that the order will not be the one you have shown in your expected result. If order is important then you should SELECT from this UNION and add an explicit ORDER BY clause on this outer select. Something like:

SELECT * FROM (
    SELECT RecordID, UserName, NULL AS CustomerName FROM UserRecord
    UNION
    SELECT RecordID, NULL AS UserName, CustomerName FROM CustomerRecord
) ORDER BY RecordID, UserName, CustomerName
Frazz
  • 2,995
  • 2
  • 19
  • 33