how to select rows from table or corresponding rows exist in subtable-CodePudding

I am not sure how to get all the rows from the parent table that also exist in the child table. For example,

parent table:

pid | description
1   | hi
2   | hello
3   | haha
4   | huhu
5   | hoho

child table

pid | cid
1   | 2
1   | 3
3   | 5

So, for pid1, we also see it in the child table with cid 2 and 3. also, for pid3, we see it in the child table with cid 5. I want to retrieve all the rows for example, where the search query is description = hi, it returns rows of pid 1, 2 and 3. If pid is not in the child table, we just retrieve from parent table only.

CodePudding user response：

You can do:

select t.*
from parent p
join child c on c.pid = p.pid
join parent t on t.pid = p.pid or t.pid = c.cid
where p.description = 'hi'

CodePudding user response：

Try the following:

with cte as 
(
select pid,description from parent where description='hi'
)
select * from cte 
union
select parent.pid, parent.description from parent 
join child on
parent.pid=child.cid
join cte on
cte.pid=child.pid

See the result from db-fiddle.

The using of UNION is to ensure the selecting of required data rows from parent table even if the parent.pid value is not existed in child.cid column, i.e. description='huhu' in the provided dataset.