JOIN two SELECT without UNION

0
votes

It needs to download the ID, name and surname of those who have registered by the deadline and have an identity card (with the relevant conditions) or passport (with the relevant conditions). ID card and passport are two separate tables.

I have made the SQL queries in UNION format and it works:

select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
join persons.id_card idd on d.id_card_id = idd.id
join persons.id_card_to_registration ir on idd.id = ir.id_card
join registrations.registration r on ir.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and ir.status in (0,5)
UNION
select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
join persons.passport pass on d.passport_id = pass.id
join persons.passport_country pc on pc.id = pass_country_id
join persons.passport_to_registration pr on pass.id = pr.passport_id 
join registrations.registration r on pr.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and pc.zone in (0,1) or (pc.zone is null and pass.safe = true);

I would now like to do this SQL in one query without union and unfortunately it doesn't work for me - I tried to do it like this:

select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
left join persons.id_card idd on d.id_card_id = idd.id
left join persons.id_card_to_registration ir on idd.id = ir.id_card
left join persons.passport pass on d.passport_id = pass.id
left join persons.passport_country pc on pc.id = pass_country_id
left join persons.passport_to_registration pr on pass.id = pr.passport_id 
join registrations.registration r on ir.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and (ir.status in (0,5) or ir.status is null)
and pc.zone in (0,1) or (pc.zone is null and pass.safe = true)

And it doesn't return any records to me. I would like some advice on what error I have made. And is it possible to create such a query without union?

sqlpostgresql