连接表返回不同的记录

Question

我试图找出如何从patientcase表从下面的报表查询返回不同的记录：

select distinct rp.patientid 
    p.mdpa11cd as mma, p.mdpa12cd as mmb, 
    test.mica1cd as mmc, test.mica2cd as mmdr, 
    lastnm, firstnm, hospitalid, ssnbr, 
    rp.relationshiptypecd, 
    isnull(p.ma1eqcd, '') A1s, 
    isnull(p.ma2eqcd, '') A2s, 
    isnull(p.mb1eqcd, '') B1s, 
    isnull(p.mb2eqcd, '') B2s, 
    isnull(p.mc1eqcd, '') C1s, 
    isnull(p.mc2eqcd, '') C2s, 
    isnull(p.mdrb11eqcd, '') DR1s, 
    isnull(p.mdrb12eqcd, '') DR2s, 
    isnull(p.mdqb11eqcd, '') DQ1s, 
    isnull(p.mdqb12eqcd, '') DQ2s, 
    isnull(p.mdpb11eqcd, '') DP1s, 
    isnull(p.mdpb12eqcd, '') DP2s, 
    isnull(p.mdrb31eqcd, '') DRB31s, 
    isnull(p.mdrb32eqcd, '') DRB32s, 
    isnull(p.mdrb41eqcd, '') DRB41s, 
    isnull(p.mdrb42eqcd, '') DRB42s, 
    isnull(p.mdrb51eqcd, '') DRB51s, 
    isnull(p.mdrb52eqcd, '') DRB52s, 
    p.ma1cd, p.ma2cd, p.mb1cd, p.mb2cd, p.mc1cd, p.mc2cd, 
    p.mdrb11cd, p.mdrb12cd, p.mdqb11cd, p.mdqb12cd, p.mdpb11cd, 
    p.mdpb12cd, p.mdrb31cd, p.mdrb32cd, p.mdrb41cd, p.mdrb42cd, 
    p.mdrb51cd, p.mdrb52cd, p.lastmolecularsampledt, 
    isnull(rp.mamismatchcd, '') MMa, 
    isnull(rp.mbmismatchcd, '') MMb, 
    isnull(rp.mcmismatchcd, '') MMc, 
    isnull(rp.mdrb1mismatchcd, '') MMdr, 
    isnull(rp.mdqb1mismatchcd, '') MMdq, 
    rp.mdpb1mismatchcd MMdpb1, 
    isnull(rp.mamismatchantigencd, '') Ma, 
    isnull(rp.mbmismatchantigencd, '') Mb, 
    isnull(rp.mcmismatchantigencd, '') Mc, 
    isnull(rp.mdrb1mismatchantigencd, '') Mdr, 
    isnull(rp.mdqb1mismatchantigencd, '') Mdq, 
    rp.mdpb1mismatchantigencd Mdpb1, suppressnameind, patienttypecd, 
    isnull(p.mdqa11eqcd, '') DQA1s, 
    isnull(p.mdqa12eqcd, '') DQA2s, 
    p.mdqa11cd, p.mdqa12cd, rp.mdqa1mismatchcd MMdqa1, 
    rp.mdqa1mismatchantigencd Mdqa1, p.mbw1cd, p.mbw2cd, 
    rp.haplotype1cd, rp.haplotype2cd 
from 
    patientcase 
inner join 
    relatedpatient rp on rp.caseid = patientcase.caseid 
inner join 
    patient p on rp.relatedpatientid = P.patientid 
left join 
    sample on sample.patientid = p.patientid 
left join 
    test on test.sampleid = sample.sampleid 
where 
    patientcase.caseid = `<Patient Cases>` 
    and rp.relatedpatientid IN `(<Donor>)` 
order by 
    rp.ordernbr asc, sample.sampledt desc 

我试图改变加入到离开，但没有运气。请建议如何使这项工作。谢谢

Answer 1

这是什么意思“patientcase表中的不同记录”在这里？

您的记录不是来自patientcase表，认为它们来自一种匿名，未命名的表，它结合了由您的连接条件定义的记录 - 那么在这种情况下什么是“不同的”？

如果你想要做你从patientcase加入上重复的记录 - 比它更简单：选择/从/加入与子查询变窄源表的方式，是这样的：

select 
    p.mdpa11cd as mma, p.mdpa12cd as mmb, 
    test.mica1cd as mmc, test.mica2cd as mmdr, 
    pcd.lastnm, pcd.firstnm, pcd.hospitalid, pcd.ssnbr, 
    rp.relationshiptypecd, 
    ... 
from 
    (SELECT DISTINCT lastnm, firstnm, hospitalid, ssnbr, caseid, ... FROM patientcase) pcd 
inner join 
    relatedpatient rp on rp.caseid = pcd.caseid 
... 

如果是不是你想要的 - 请详细说明，因为它是从明确的方式。