SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Join between 3 Tables Problem with Select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

obedrodriguez
Starting Member

9 Posts

Posted - 02/20/2013 :  19:56:25  Show Profile  Reply with Quote
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

3332 Posts

Posted - 02/20/2013 :  20:57:22  Show Profile  Reply with Quote
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;
Go to Top of Page

obedrodriguez
Starting Member

9 Posts

Posted - 02/21/2013 :  08:38:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 02/21/2013 :  09:01:09  Show Profile  Reply with Quote
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 - 02/21/2013 :  09:13:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 02/21/2013 :  09:18:52  Show Profile  Reply with Quote
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 - 02/21/2013 :  09:35:35  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 02/21/2013 :  14:52:34  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 02/21/2013 :  15:53:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 02/21/2013 :  16:29:30  Show Profile  Reply with Quote
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 - 02/26/2013 :  08:41:48  Show Profile  Reply with Quote
yes thats true James K

Edited by - obedrodriguez on 02/26/2013 08:44:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000