Multiple joins subquery SQL Server 2008

On the left you'll see my design for one table, on the right you'll see the results from the subquery in the SQL below. I'm trying to join the subquery on the three fields patientID,claimsFromDate,claimsThroughDate on tblClaims, and have the outer query associate the correct tblClaims.ID with the three part join.

The errors I'm getting:

line 3, incorrect syntax near the keyword selectandincorrect syntax near the ), line 12

select tblClaims.id, t.primaryCode
from t
(
select patientid, claimsfromdate, claimsthroughDate, primarycode from myTable
union
select patientid, claimsfromDate, claimsthroughDate, secondaryCode from myTable
union
select patientID, claimsfromdate, claimsthroughDate, tertiarycode from myTable

) as t
inner join t on tblclaims.patientid=t.patientid 
and tblclaims.claimsfromdate=t.claimsfromdate
and tblclaims.cllaimsthroughdate=t.claimsfromdate

EDIT: The inner query is to reconcile a multi column field. It returns 1.5 million rows. the fixed query I ran returned 3.5 million which was

select tblClaims.id, t.primarycode from ( select patientid, claimsfromdate, claimsthroughDate, primarycode from myTable ) as t inner join tblclaims on tblclaims.patientid=t.patientid and tblclaims.claimsfromdate=t.claimsfromdate and tblclaims.cllaimsthroughdate=t.claimsfromdate

Answers


Try this:

select tblClaims.id, t.primarycode 
from 
(
select patientid, claimsfromdate, claimsthroughDate, primarycode from myTable
) as t
inner join tblclaims on tblclaims.patientid=t.patientid 
and tblclaims.claimsfromdate=t.claimsfromdate
and tblclaims.cllaimsthroughdate=t.claimsfromdate

Did you try using Distinct ?

select DISTINCT tblClaims.id, t.primarycode 
from 
(
    select patientid, claimsfromdate, claimsthroughDate, primarycode from myTable
) as t
inner join tblclaims on tblclaims.patientid=t.patientid 
and tblclaims.claimsfromdate=t.claimsfromdate
and tblclaims.cllaimsthroughdate=t.claimsfromdate

Need Your Help

Static and Media files in django 1.5

python django django-1.5 django-staticfiles

I am making a site, I am the only one that will be uploading anything to the site. It seems like its more complicated to have two separate directories media and static would it be unreasonable to j...

iTextSharp PDF To Text Header and Footer Read Issue

asp.net vb.net pdf itextsharp itext

I am try Convert PDF(.pdf) to Text(.txt) Using iTextSharp Library,But in Text File Extra space add between words,