MySQL Forums
Forum List  »  Newbie

Problem with this Query
Posted by: marcello marcello
Date: June 23, 2021 08:44AM

Hi, I am having a trouble with an specific issue hope if anyone can help.

Basically I have two tables, one has DNA results and the other Markers that has the ID to join all the DNA type ( Allele ).

The table dna_results has to self join since I wish to match the result of the son, with the mother and the father ( this part its done ) so I have the Trio Matching.

But I am also trying to get the dna_result from the son that only matches with the father or the mother that are not in the matching trio ( in the same query ), since that might be a DNA result that only matches with the mother and not the father ( HE IS NOT THE FATHER ! ).

My query :
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
from dna_results
left join snp_markers on dna_results.snp_marker_id = snp_markers.id
left join dna_results as mother_resultson snp_results.snp_marker_id = mother_results.snp_marker_id
left join dna_results as father_resultson snp_results.snp_marker_id = father_results.snp_marker_id
where dna_results.dna_result_id = 85396 -- son dna result id
and mother_results.dna_result_id = 35303 -- mother dna result id
and father_results.dna_result_id = 23592 -- father dna result id
and snp_markers.is_parentage_excluded = false -- extra checking
order by dna_results.snp_marker_id asc;

Options: ReplyQuote


Subject
Written By
Posted
Problem with this Query
June 23, 2021 08:44AM


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.