2

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 |
  • Would it be ok to get a single row for each child with the parent filled but no row for the parent alone? That would avoid the union and allows you to select by child Id. From your sample data there is no parent without a child... – eckes Sep 6 '18 at 23:12
  • I need the parent row to be able to include the parent name for my order by name. – Logic Sep 7 '18 at 0:46
  • If you join them you have both names in one row. – eckes Sep 7 '18 at 0:47
  • I updated my original post to visualize what I was going for. – Logic Sep 7 '18 at 1:53
  • What about „1 | 1 | Parent A | Child A“ instead with a simple left join – eckes Sep 7 '18 at 2:10
0

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';
-1

Debugged using source data from author's fiddle.

UNION SELECT Result

    SELECT NULL Child_Id, Parent.Id Parent_Id, Parent_Name, NULL Child_Name
    FROM Parent
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name
    FROM Child 
;

Using WHERE ChildId = '1';

SELECT NULL Child_Id, Parent.Id Parent_Id, Parent_Name, NULL Child_Name
    FROM Parent, Child
    WHERE Parent.Id = Child.Parent_Id
      AND Child.Id = 1
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name
    FROM Child 
    WHERE Id = 1
;

Is there anway to have the Where ChildId =1; out side the union?

SELECT Child_Id, Parent_Id, Parent_Name, Child_Name
FROM (
    SELECT NULL Child_Id, Parent.Id Parent_Id, Parent.Parent_Name, NULL Child_Name, Child.Id Id
    FROM Parent
    RIGHT JOIN Child ON Parent.Id = Child.Parent_Id
UNION ALL
    SELECT Id, Parent_Id, NULL, Child_Name, Id
    FROM Child 
    ) subquery
WHERE Id = 1
;

fiddle

  • Thanks. Is there anway to have the Where ChildId =1; out side the union? See this fiddle (sqlfiddle.com/#!9/f9f1c6/1), I was thinking of using it with right join. Apologies as I haven't made myself clear on my original post. – Logic Sep 6 '18 at 14:36
  • @Logic View for debugged results. Remember - the variant with single filtering is more expensive then the variant with separate filterings because the full result of subquery must be obtained (and cached on disk) before output query execution. – Akina Sep 7 '18 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.