| Author |
 Topic  |
|
|
obedrodriguez
Starting Member
9 Posts |
 Posted - 02/20/2013 : 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
|
Edited by - obedrodriguez on 02/21/2013 12:54:26
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/20/2013 : 20:57:22
|
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; |
 |
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 02/21/2013 : 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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/21/2013 : 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___ |
|
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 02/21/2013 : 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 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/21/2013 : 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)? |
|
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 02/21/2013 : 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 |
Edited by - obedrodriguez on 02/21/2013 09:48:10 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/21/2013 : 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
); |
|
|
|
bitsmed
Yak Posting Veteran
Denmark
98 Posts |
Posted - 02/21/2013 : 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
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1490 Posts |
Posted - 02/21/2013 : 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__
|
|
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 02/26/2013 : 08:41:48
|
yes thats true James K
|
Edited by - obedrodriguez on 02/26/2013 08:44:48 |
|
|
| |
Topic |
|
Join between 3 Tables Problem with Select
