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

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

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

Code:

---

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 = ?

---

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.

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:

Code:

---

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

---

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

Code:

---

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));

---

Ah.. there are extra conditions!

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

Code:

---

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:

Code:

---

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.