join tables using the foreign key in the traits table where it existed (e.g. variable_id, site_id, cultivar_id rather than to, e.g., variables.id.
e.g. I relaced traits left join sites on traits.site_id = sites.id left join experiments_sites on sites.id = experiments_sites.site_id, it is now left join experiments_sites on sites.id = ...
I joined both experiments_treatments and treatments to traits.treatment_id
This fixed joining each row to two different treatments. Then I rearranged the joins to join both experiments_sites and experiments_treatments to traits.site_id and traits.treatment_id and only then join to experiments.id
see https://github.com/az-digitalag/organization/issues/438
updated the query to:
traitstable where it existed (e.g. variable_id, site_id, cultivar_id rather than to, e.g., variables.id.traits left join sites on traits.site_id = sites.id left join experiments_sites on sites.id = experiments_sites.site_id, it is nowleft join experiments_sites on sites.id = ...experiments_treatmentsandtreatmentstotraits.treatment_idThis fixed joining each row to two different treatments. Then I rearranged the joins to join both experiments_sites and experiments_treatments to traits.site_id and traits.treatment_id and only then join to experiments.id