How to make a select query for an entity when all I know is a value of a column in a child table?
For example, I have a table crop

crop_id  | name
------------------
1        | Fruit
2        | Berry
3        | Vegetable

And I have a table harvest

harvest_id | crop_id | name
----------------------------
1          | 1       | Apple
2          | 2       | Blueberry
3          | 1       | Orange

This is a one-to-many relationship between crop and harvest.
Now I only know the value 'Apple' and I want to make a query that selects a single row from crop and joins each child row that is of the same 'type' as 'Apple'.

The following query would only yield Fruit with Apple, but I would like Fruit with Apple and Orange

select *
from crop c
left outer join harvest h on c.id=h.crop_id
where h.name = 'Apple'

Can I do this with a single select query? I do not wish to make 2 separate queries, one for Apple to get its crop_id and then another one for the crop by id with children.

CodePudding user response：

As I understand, you want using only 'Apple' to find all fruits?

select *
from crop c
left outer join harvest h on c.crop_id=h.crop_id
where h.crop_id = (select crop_id from harvest where name = 'Apple')

Fiddle

CodePudding user response：

Is this what you're aiming for? You can get the crop_id with your query. Then you can use the result as a select query in another query.

select *
from crop c
where c.id = (select h.crop_id from crop c left outer join harvest h on c.id=h.crop_id where h.name = 'Apple')

CodePudding user response：

Join crop to harvest, then to another harvest.

select h2.*, c.name as crop_name
from harvest h1
join crop c using (crop_id) 
left join harvest h2 using (crop_id) 
where h1.name = 'Apple'
order by h2.harvest_id;

Test on db<>fiddle here