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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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

3873 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

3873 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
Aged Yak Warrior

545 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

3873 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  
 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.17 seconds. Powered By: Snitz Forums 2000