Getting 1054 Unknown Column Error in MySQL

Question

When running the following query I get a 1054 error stating that the unknown column is 'rct.trial_id' in the field list. But as far as I can tell, my syntax is correct. Any help would be appreciated!

select 
r.id,
rv.segment_cytoband,
rv.variant_category,
rv.gene,
rv.variant,
ra.sensitive_tier1,
ra.contraindicated_tier1,
ra.sensitive_tier2,
ra.contraindicated_tier2,
rv.cdna,
rv.type as 'Variant Type',
r.last_published,
r.status,
spm.property_name,
sp.property_value as 'oncotreeCode',
r.tumor_cellularity,
s.lims_sample_id,
sr.sample_run_lims_id,
count(distinct rct.trial_id) as 'No. of Trials 1',
count(distinct ract.trial_id) as 'No. of Trials 2'
from report r
LEFT JOIN report_variants rv ON r.id = rv.report_id
LEFT JOIN report_assays ra ON r.id = ra.report_id
LEFT JOIN report_samples rs ON r.id = rs.report_id
LEFT JOIN sample_run sr ON rs.sample_run_id = sr.id
LEFT JOIN sample s ON sr.sample_id = s.id
LEFT JOIN run rn ON sr.run_id = rn.id
LEFT JOIN sample_properties_master spm ON rn.run_combination_id = spm.run_combination_id
LEFT JOIN sample_properties sp ON sr.sample_id = sp.sample_id
LEFT JOIN 
    (select rv.report_id, count(distinct rct.trial_id) from report_clinical_trials rct
        INNER JOIN report_variants rv on rv.id = rct.report_variant_id) as rcttrial ON rv.id = rct.report_variant_id
LEFT JOIN
    (select ra.report_id, count(distinct ract.trial_id) from report_assays_clinical_trials ract
        INNER JOIN report_assays ra on ra.id = ract.report_assay_id) as racttrial ON ra.id = ract.report_assay_id
where r.status = 'REPORT EXPORTED' 
having spm.property_name like 'oncotreeCode';

Answer 1

You can't access tables in a subquery from the main query.

You need to give an alias to count(distinct rct.trial_id) in the subquery, then use that alias in the main query. And the same for ract.trial_id.

select 
    r.id,
    rv.segment_cytoband,
    rv.variant_category,
    rv.gene,
    rv.variant,
    ra.sensitive_tier1,
    ra.contraindicated_tier1,
    ra.sensitive_tier2,
    ra.contraindicated_tier2,
    rv.cdna,
    rv.type as 'Variant Type',
    r.last_published,
    r.status,
    spm.property_name,
    sp.property_value as 'oncotreeCode',
    r.tumor_cellularity,
    s.lims_sample_id,
    sr.sample_run_lims_id,
    rcttrial.count as 'No. of Trials 1',
    racttrial.count as 'No. of Trials 2'
from report r
LEFT JOIN report_variants rv ON r.id = rv.report_id
LEFT JOIN report_assays ra ON r.id = ra.report_id
LEFT JOIN report_samples rs ON r.id = rs.report_id
LEFT JOIN sample_run sr ON rs.sample_run_id = sr.id
LEFT JOIN sample s ON sr.sample_id = s.id
LEFT JOIN run rn ON sr.run_id = rn.id
LEFT JOIN sample_properties_master spm ON rn.run_combination_id = spm.run_combination_id
LEFT JOIN sample_properties sp ON sr.sample_id = sp.sample_id
LEFT JOIN 
    (select rv.report_id, count(distinct rct.trial_id) AS count from report_clinical_trials rct
        INNER JOIN report_variants rv on rv.id = rct.report_variant_id) as rcttrial ON rv.id = rct.report_variant_id
LEFT JOIN
    (select ra.report_id, count(distinct ract.trial_id) AS count from report_assays_clinical_trials ract
        INNER JOIN report_assays ra on ra.id = ract.report_assay_id) as racttrial ON ra.id = ract.report_assay_id
where r.status = 'REPORT EXPORTED' 
having spm.property_name like 'oncotreeCode';

Answer 2

select 
r.id,
rv.segment_cytoband,
rv.variant_category,
rv.gene,
rv.variant,
ra.sensitive_tier1,
ra.contraindicated_tier1,
ra.sensitive_tier2,
ra.contraindicated_tier2,
rv.cdna,
rv.type as 'Variant Type',
r.last_published,
r.status,
spm.property_name,
sp.property_value as 'oncotreeCode',
r.tumor_cellularity,
s.lims_sample_id,
sr.sample_run_lims_id,
rcttrial.count as 'No. of Trials 1',
racttrial.count as 'No. of Trials 2'
from report r
LEFT JOIN report_variants rv ON r.id = rv.report_id
LEFT JOIN report_assays ra ON r.id = ra.report_id
LEFT JOIN report_samples rs ON r.id = rs.report_id
LEFT JOIN sample_run sr ON rs.sample_run_id = sr.id
LEFT JOIN sample s ON sr.sample_id = s.id
LEFT JOIN run rn ON sr.run_id = rn.id
LEFT JOIN sample_properties_master spm ON rn.run_combination_id = spm.run_combination_id
LEFT JOIN sample_properties sp ON sr.sample_id = sp.sample_id
LEFT JOIN 
    (select rv.report_id, count(distinct rct.trial_id) AS count from report_clinical_trials rct
        INNER JOIN report_variants rv on rv.id = rct.report_variant_id) as rcttrial ON r.id = rcttrial.report_id
LEFT JOIN
    (select ra.report_id, count(distinct ract.trial_id) AS count from report_assays_clinical_trials ract
        INNER JOIN report_assays ra on ra.id = ract.report_assay_id) as racttrial ON r.id = racttrial.report_id
where r.status = 'REPORT EXPORTED' 
having spm.property_name like 'oncotreeCode';