Join between 3 Tables Problem with Select

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

srobinson 596 Mr or Mrs. 500 Points: 539 More actions · Answer 1

Well - based on the results you want - it doesn't look like you need table 2 at all.

Something like this would get you the results you need...

select t1.sampleid,t1.classid,t2.resultid,t2.testid

from samples t1 left join results t2 on t1.sampleid = t2.sampleid

obedrodriguez7 Newbie Points: 2 More actions · Answer 2

With that qry I have this result and its not the expected.

SampleID|ClassID|ResultID|TestID

___A____|___X__|___R1___|___1____

___A____|___X__|___R2___|___4____

___A____|___Y__|___R1___|___1____

___A____|___Y__|___R2___|___4____

___A____|___Z__|___R1___|___1____

___A____|___Z__|___R2___|___4____

I want this

SampleID|ClassID|ResultID|TestID

___A____|___X___|___R1___|___1____

___A____|___Y___|___R2___|___4____

___A____|___Z___|__NULL__|__NULL__

Thank you

mickyT SSChampion Points: 10360 More actions · Answer 3

Hi

Try this, it should give the result you want

with Samples as (

-- Sample Data

SELECT * FROM (VALUES ('A','X'), ('A', 'Y'), ('A','Z')) AS S(SampleID, ClassID)

)

,Tests as (

SELECT * FROM (VALUES(1,'X'),(2,'X'),(3,'X'),(4,'Y'),(5,'Z')) AS T(TestID,ClassID)

)

,Results as (

SELECT * FROM (VALUES('R1','A',1),('R2','A',4)) AS R(ResultID,SampleID,TestID)

)

--Query

SELECT S.SampleID, S.ClassID, R.ResultID, T.TestID

FROM Samples S

LEFT OUTER JOIN ( -- OUTER JOIN the INNER JOIN of the Results and Tests tables

Results R

INNER JOIN Tests T ON T.TestID = R.TestID

) ON S.ClassID = T.ClassID AND S.SampleID = R.SampleID