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;SamplesSampleID|ClassID ___A____|____X________A____|____Y________A____|____Z_____TestsTestID|ClassID ___1__|____X________2__|____X________3__|____X________4__|____Y________5__|____Z_____ResultsResultID|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.TestIDFROM Samples s INNER JOIN Tests t ON t.ClassID = s.ClassID LEFT JOIN Results r ON r.TestID = t.TestID;[/code] |
|
|
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 |
|
|
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:ResultsResultID|SampleID|TestID___R1___|____A___|___2______R2___|____A___|___4___ResultsResultID|SampleID|TestID___R1___|____A___|___1______R1___|____A___|___2______R2___|____A___|___4___ |
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 2013-02-21 : 09:13:58
|
JamesI 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
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)? |
|
|
obedrodriguez
Starting Member
9 Posts |
Posted - 2013-02-21 : 09:35:35
|
JamesFor this table you writed.ResultsResultID|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 posiblityResultID|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 |
|
|
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 c1WHERE (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
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 TestIDFROM 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
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:ResultsResultID|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 - 2013-02-26 : 08:41:48
|
yes thats true James K |
|
|
|