Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join between 3 Tables Problem with Select

Author  Topic 

obedrodriguez
Starting Member

9 Posts

Posted - 2013-02-20 : 19:56:25
Hello guys I need your help one more time. I have these 3 tables, Im trying to join these tables with a select statement to get my result and something is wrong. These are the tables;

Samples
SampleID|ClassID
___A____|____X_____
___A____|____Y_____
___A____|____Z_____
Tests
TestID|ClassID
___1__|____X_____
___2__|____X_____
___3__|____X_____
___4__|____Y_____
___5__|____Z_____
Results
ResultID|SampleID|TestID
___R1___|____A___|___1___
___R2___|____A___|___4___


What I want in the result of the select is all the content in samples with the details like this one:

SampleID|ClassID|ResultID|TestID
___A____|___X___|___R1___|___1____
___A____|___Y___|___R2___|___4____
___A____|___Z___|__NULL__|__NULL__


Thanks


James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-20 : 20:57:22
[code]SELECT
s.SampleID,
s.ClassID,
r.ResultID,
r.TestID
FROM
Samples s
INNER JOIN Tests t ON t.ClassID = s.ClassID
LEFT JOIN Results r ON r.TestID = t.TestID;[/code]
Go to Top of Page

obedrodriguez
Starting Member

9 Posts

Posted - 2013-02-21 : 08:38:46
With that qry im not getting the correct result that i want. Im getting this;

SampleID|ClassID|ResultID|TestID
____A___|___X___|___R1___|__1___
____A___|___X___|__NULL__|_NULL_
____A___|___X___|__NULL__|_NULL_
____A___|___Y___|___R2___|__4___
____A___|___Z___|__NULL__|_NULL_

If i put distinct I will get this and is wrong either.

SampleID|ClassID|ResultID|TestID
____A___|___X___|__NULL__|_NULL_
____A___|___X___|___R1___|__1___
____A___|___Y___|___R2___|__4___
____A___|___Z___|__NULL__|_NULL_



I dont know what is going on.
Thanks

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 09:01:09
The issue is that in one case (for class ID = Z) you want to include a row in the results even though there is no corresponding row in the Results table, yet in some other cases (for classID = X) you do not want to do that. We can fix the code for this specific sample to give you what you want, but what I don't know is what you would need to get if your results table where either of the two that I am showing below:

Results
ResultID|SampleID|TestID
___R1___|____A___|___2___
___R2___|____A___|___4___

Results
ResultID|SampleID|TestID
___R1___|____A___|___1___
___R1___|____A___|___2___
___R2___|____A___|___4___
Go to Top of Page

obedrodriguez
Starting Member

9 Posts

Posted - 2013-02-21 : 09:13:58
James

I need to include the record with class id = z. I need all the records related to the samples even though is no corresponding row in the Results table. I need then with the null in the other colums. Look this way I need all samples with results and no results. Samples with no results I need NULL in the columns of ResultID and TestID.

Result I want;

SampleID|ClassID|ResultID|TestID
___A____|___X___|___R1___|___1____
___A____|___Y___|___R2___|___4____
___A____|___Z___|__NULL__|__NULL__

Thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 09:18:52
Can you look at the two Results table examples that I posted earlier and tell me what the results should be? Do you want to get the same results in all 3 cases (the Results table you posted, and the two results tables that I posted)?
Go to Top of Page

obedrodriguez
Starting Member

9 Posts

Posted - 2013-02-21 : 09:35:35
James

For this table you writed.
Results
ResultID|SampleID|TestID
___R1___|____A___|___2___
___R2___|____A___|___4___

I want this;

SampleID|ClassID|ResultID|TestID
___A____|___X___|___R1___|___2____
___A____|___Y___|___R2___|___4____
___A____|___Z___|__NULL__|__NULL__


For the other one you writed we dont have a posibility to get that new row you added. But we can have this posiblity

ResultID|SampleID|TestID
___R1___|____A___|___1___
___R2___|____A___|___2___
___R3___|____A___|___4___

and the result I want is;

SampleID|ClassID|ResultID|TestID
___A____|___X___|___R1___|___1____
___A____|___X___|___R2___|___2____
___A____|___Y___|___R3___|___4____
___A____|___Z___|__NULL__|__NULL__


Thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 14:52:34
Can you try this? I am sort of patching up the results after the fact. There may be a better way though:
;WITH cte AS
(
SELECT
s.SampleID,
s.ClassID,
r.ResultID,
r.TestID
FROM
Samples s
INNER JOIN Tests t ON t.ClassID = s.ClassID
LEFT JOIN Results r ON r.TestID = t.TestID
)
SELECT * FROM cte c1
WHERE (c1.ResultId IS NOT NULL AND c1.TestId IS NOT NULL)
OR
NOT EXISTS
(
SELECT * FROM cte c2 WHERE c2.sampleId = c1.sampleId AND c1.classId = c2.classid
AND c2.resultid IS NOT NULL AND ct2.testid IS NOT NULL
);
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-02-21 : 15:53:31
Maybe this:

SELECT
s.SampleID,
s.ClassID,
max(r.ResultID) as ResultID,
max(r.TestID) as TestID
FROM
Samples s
INNER JOIN Tests t ON t.ClassID = s.ClassID
LEFT JOIN Results r ON r.TestID = t.TestID;
GROUP BY
s.SampleID,
s.ClassID

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 16:29:30
If we use MAX functions bitsmed, then for the example that I was asking about below, we would get only one row back, but obedrodriguez wants two rows in that case:

Results
ResultID|SampleID|TestID
___R1___|____A___|___1___
___R1___|____A___|___2___
___R2___|____A___|___4___

SampleID|ClassID|ResultID|TestID
___A____|___X___|___R1___|___1____
___A____|___X___|___R2___|___2____
___A____|___Y___|___R3___|___4____
___A____|___Z___|__NULL__|__NULL__

Go to Top of Page

obedrodriguez
Starting Member

9 Posts

Posted - 2013-02-26 : 08:41:48
yes thats true James K
Go to Top of Page
   

- Advertisement -