MySQL Forums
Forum List  »  Newbie

Re: Problem with this Query
Posted by: Peter Brawley
Date: June 23, 2021 09:51AM

1 How can it be meaningful to try to match with the "father" if he's not the father!?

2 Your Where subclauses referencing mother_results and father_results effectively turn your Left self-Joins into Inner Joins. Since you don't know what's there till you see it, I suggest you start with a more inclusive query to see what's there, eg ...

select
  dna_results.snp_marker_id,
  dna_results.allele_1 as son_a1,
  dna_results.allele_2 as son_a2,
  mother_results.allele_1 as mother_a1,
  mother_results.allele_2 as mother_a2,
  father_results.allele_1 as father_a1,
  father_results.allele_2 as father_a2,
  mother_results.dna_result_id,
  father_results.dna_result_id,
  snp_markers.is_parentage_excluded
from dna_results
left join snp_markers                   on dna_results.snp_marker_id = snp_markers.id
left join dna_results as mother_results on snp_results.snp_marker_id = mother_results.snp_marker_id
left join dna_results as father_results on snp_results.snp_marker_id = father_results.snp_marker_id
where dna_results.dna_result_id = 85396         -- son dna result id
order by dna_results.snp_marker_id asc;

... then tailor your query accordingly.

3 It may be difficult or impossible to get all the info you want from one query. Remember, there are occasions when multiple queries offer the best solution.

Options: ReplyQuote


Subject
Written By
Posted
Re: Problem with this Query
June 23, 2021 09:51AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.