0

Is there a way to retrieve a set of rows from a table but also including (join, union?) rows from the same table whose ids are referenced in the initial selection? Here's an example.

ID       Name       Surname         UserID    ParentID
---------------------------         ------------------
1        Daniel     Brown           1         0
2        John       Brown           2         4
3        Andrew     Brown           3         5
4        Simon      Smith           4         0
5        Peter      Smith           5         1

So I want to retrieve all the rows where the surname is Brown, and also all the rows pertaining to the parent value. In this example, all rows would be returned.

I would like to do this in one query, if it's possible. Note, I would not want to return Peter Smith's parent row (1) as well. Just the initial search (all the Browns) and the relevant parents to that initial search.

Dan
  • 1,154
  • 1
  • 7
  • 14

1 Answers1

0

You can try below -

DEMO

select id,name,surname,parent from tablename
where surname='Brown'
union 
select a1.id,a1.name,a1.surname,a1.parent from tablename a join tablename a1
on a.parent=a1.id
where a.surname='Brown'

OUTPUT:

id  name    surname parent
1   Daniel  Brown   0
2   John    Brown   5
3   Andrew  Brown   4
4   Simon   Smith   0
5   Peter   Smith   1
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • 1
    Ignore the previous comment; +1; you need UNION instead of UNION ALL, because duplicates may appear, if more people have same parent – Madhur Bhaiya Oct 02 '19 at 13:40
  • 1
    This is great, but I may have over-simplified the question, as in reality, the parentID column is being joined from a separate table, so is unavailable for the union. I will update the question to reflect the slightly more complex situation. – Dan Oct 02 '19 at 14:25