Search code examples
mysqljoincross-join

Unique values in same column from 2 unrelated tables in mysql query

I have 2 tables called 'parents' and 'children'.

parents

parent_id | name |

children

child_id | parent_id | child_name |

I am looking for a query which gives the output like so:

parent_id(from parents) | name (unique names from both tables) | is_parent (1, if record is from parent table).

I tried using following from one of the stacks:

SELECT
   T1.name, T2.child_name
FROM
   parents T1
   LEFT OUTER JOIN
   children T2 ON T1.name = T2.child_name
UNION
SELECT
   T1.name, T2.child_name
FROM
   parents T1
   RIGHT OUTER JOIN
   children T2 ON T1.name = T2.child_name

But it gives be 2 separate columns and not a merged, single column.

Appreciate your help.

Edit

Adding example:

enter image description here

Solution

  • There is no need for joins, just UNION ALL:

    select parent_id, name, 1 is_parent from Parents
union all
select parent_id, child_name, 0 from Children

    See the demo.
    Results:

    | parent_id | name    | is_parent |
| --------- | ------- | --------- |
| 1         | Raja    | 1         |
| 2         | Sahil   | 1         |
| 3         | Ramesh  | 1         |
| 4         | Suresh  | 1         |
| 1         | Riya    | 0         |
| 1         | Rakesh  | 0         |
| 2         | Abhay   | 0         |
| 2         | Vishnu  | 0         |
| 3         | Rakesh  | 0         |
| 3         | Sunitha | 0         |