Mysql – Retrieve parent and child data using child id

MySQLmysql-5.6

I have a parent and child table, I am able to retrieve all data using UNION SELECT.

Parent Table:

id | parent_name
 1 | Parent A
 2 | Parent B

Child Table:

id | parent_id| child_name
 1 |        1 | Child A
 2 |        2 | Child B
 3 |     NULL | Child C

Using this query (http://sqlfiddle.com/#!9/f9f1c6/1)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name 
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name 
  from Child
) parent on parent.parent_id = c.parent_id;

I get this result:

child_id| parent_id|         name|
   NULL |        1 |    Parent A |
      1 |        1 |     Child A | 
   NULL |        2 |    Parent B | 
      2 |        2 |     Child B |
      3 |     NULL |     Child C |

This is where I'm kinda lost.
I want to retrieve a child that will still include the parent data.

Using WHERE ChildId = '1'; outside of join.

Expected result:

child_id| parent_id|        name |
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |

Best Answer

I was able to figure out what I wanted to achieve.

I just added an OR on right join. (http://sqlfiddle.com/#!9/f9f1c6/12)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name 
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name 
  from Child
) parent on parent.parent_id = c.parent_id OR parent.child_id = c.id
WHERE c.id = '2';
Related Question