[RESOLVED] [SQL Server 2005] SQL question Complicated join

[bmahler]

Ok here is where I am at. I have the following tables
LabAddress
--LabAddressPK
--etc...

ACToAddress
--ACToAddressPK
--LabAddressPK
--AnalysisCodePK

SampleAC
--SampleACPK
--SamplePK
--AnalysisCodePK

Samples
--SamplePK
--etc...

Essentially what it is is that I have Labs that can have multiple addresses. Assigned to these addresses are multiple Analysis Codes they can perform.

I then have samples that have specific Analysis Codes assigned to them.

What I need to be able to return is the samples that a specific lab address can analyze. So the input would be the LabAddressPk and I need to return all the SamplePK that that lab address can perform. So I am kind of lost on how to do this and any help would be appreciated.

I hope I explained what I am trying to do here well enough and if not please let me know.

Thanks,

Brian

[si_the_geek]

By simply joining one table at a time, I came up with this:

SELECT S.SamplePK
FROM LabAddress LA 
INNER JOIN ACToAddress AA ON (LA.LabAddressPK = AA.LabAddressPK)
INNER JOIN SampleAC SA ON (SA.AnalysisCodePK = AA.AnalysisCodePK)
INNER JOIN Samples S ON (S.SamplePK = SA.SamplePK)
WHERE LA.LabAddressPK = ?

[bmahler]

For some reason that is returning results it shouldn't. For example SamplePk 28 has analysis codes 9 and 4 assigned to it, and labAddressPK 4 can do 1 and 8 and when I run that with LabAddressPK = 4 it returns SamplePK 28 as acceptable yet it is not, because address 4 can not perform analysis code 9 or 4.

[si_the_geek]

Based purely on the table structure you showed (and assumptions of how they are supposed to link together), the Joins I posted are correct.. is there anything else we should know about the table structure?

To check that the joins are correct, I would recommend checking some data from each table, by adding more fields to the Select clause, eg:

SELECT S.SamplePK, SA.SampleACPK, AA.ACToAddressPK, LA.LabAddressPK
FROM ...

[bmahler]

I am marking this resolved because I was able to write a stored procedure to do what I was trying to accomplish. Below is the Stored Procedure I wrote

CREATE PROCEDURE [dbo].[sp_GetLocsForAddress]
	@addresspk INT
AS
	DECLARE @spks VARCHAR(MAX), @acpk INT
	SELECT SamplePK, AnalysisCodePK INTO #t_Samples FROM SampleAC WHERE SamplePK IN (SELECT SamplePK FROM Samples WHERE DateSentToLab IS NULL  AND DateCollected IS NOT NULL);
	DELETE FROM #t_Samples WHERE SamplePK IN 
	(SELECT SamplePK FROM #t_Samples WHERE AnalysisCodePK NOT IN 
	(SELECT AnalysisCodePK FROM AcToAddress WHERE LabAddressPK = @addresspk));

	SELECT LocPK,LocationName 
	FROM Locations WHERE LocPK IN 
	(SELECT LocPK FROM Samples WHERE SamplePK IN
	(SELECT SamplePK FROM #t_Samples));

[si_the_geek]

Ah.. there are extra conditions!

Here's how you could merge the Select Into and the Delete:

SELECT SAC.SamplePK, SAC.AnalysisCodePK 
INTO #t_Samples 
FROM SampleAC SAC
INNER JOIN Samples S ON (SAC.SamplePK = S.SamplePK)
WHERE S.DateSentToLab IS NULL
AND S.DateCollected IS NOT NULL
AND AnalysisCodePK NOT IN (SELECT AnalysisCodePK FROM AcToAddress WHERE LabAddressPK = @addresspk)

..and how you could merge that with the final Select:

SELECT L.LocPK, L.LocationName
FROM Locations L 
INNER JOIN Samples S ON (L.LocPK = S.LocPK)
INNER JOIN SampleAC SAC ON (SAC.SamplePK = S.SamplePK)
WHERE S.DateSentToLab IS NULL  
AND S.DateCollected IS NOT NULL
AND AnalysisCodePK NOT IN (SELECT AnalysisCodePK FROM AcToAddress WHERE LabAddressPK = @addresspk)

Note that this may return slightly different results as it is no longer using an IN clause.. I think it should be OK tho.