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 |
7
  • 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, 2018 at 23:12
  • I need the parent row to be able to include the parent name for my order by name.
    – suh_dude-
    Sep 7, 2018 at 0:46
  • If you join them you have both names in one row.
    – eckes
    Sep 7, 2018 at 0:47
  • I updated my original post to visualize what I was going for.
    – suh_dude-
    Sep 7, 2018 at 1:53
  • What about „1 | 1 | Parent A | Child A“ instead with a simple left join
    – eckes
    Sep 7, 2018 at 2:10

2 Answers 2

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

2
  • 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.
    – suh_dude-
    Sep 6, 2018 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, 2018 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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