Why joining two tables onto parent table returns empty query-CodePudding

I am new to postgres and this schema is a work in progress, there is a lot of redundant columns for data checks.

Why do I receive an empty query when I join "penn_survey" and "brazil_legacy_survey" onto the "visit" table? If I join only "penn_survey" onto the "visit" table the query works correctly and vice versa for "brazil_legacy_survey". Yet when I try join both survey tables back onto the "visit" table in a single query it returns an empty table.

Do I need different column headers or separate foreign key IDs? Is my understanding of the structure for join

-- Data Check
select v.date, v.site, ps.site, ps.date, b.site, b.date
from visit v 
join penn_survey ps on ps.visit_id = v.visit_id 
join brazil_legacy_survey b on b.visit_id = v.visit_id

-- Joining only penn_survey table
select v.date, v.site, ps.site, ps.date
from visit v 
join penn_survey ps on ps.visit_id = v.visit_id

CodePudding user response：

Expanding on the comments, using a left join to both surveys should work, however it would potentially return additional rows of data unless you add a where clause that would include and/or statements. Probably easier to just UNION two queries and control your conditions in each one.

select 'penn' as survey, v.date as visit_date, v.site as visit_site, 
  ps.date as survey_date, ps.site as survey_site
from visit v 
join penn_survey ps on ps.visit_id = v.visit_id 
union 
select 'brazil', v.date, v.site, b.date, b.site
from visit v 
join brazil_legacy_survey b on b.visit_id = v.visit_id ;