How do I form this SQL Server select statement to get the properties I want from distantly related tables?

I have an inventory table. Let’s call it INV.
I also have particular ID from another object. Let’s call it MyID.
I want a select statement that gets me a few columns from INV and one column from a table that is several steps away:

There is table B, from which we want all ‘sID’ values where ‘bID’ = MyID and SomeOtherColumn = 1.

If we take those results and then look at another table, S, we will find each sID lines up with exactly one ID from the INV table. So now we have our one-to-one relationship for each INV row to one sID.

Then we have table T. Each row in T has an sID, a tID, and a Date. Each combination of these 3 values is unique.

We also have a table called T_Lookup. tID in table T is a foreign key relating to T_Lookup. T_Lookup has a column called ‘Type’ where one possible value is ‘Approved’.

So here’s what I need to do: I need to select some INV columns and the earliest date in table T where the tID represents an ‘Approved’ Type in T_Lookup and the sID is the one that corresponds to the INV row in question.

I hope that makes sense. The final query will be something like

SELECT Aa, Bb, Cc, Date
FROM INV
LEFT JOIN (
 --some convoluted thing that ultimately just needs Date plus an inventory ID for the join
) S
ON INV.ID = S.InventoryId

It’s the ordering of things in SQL that is making this difficult for me. It’s a lot easier to just conceptualize this: "From the T table, for each sID that has SomeOtherColumn = 1 and bID = MyID in the B table, get the Date of the earliest ‘Approved’ T and attach that to the details of the INV object corresponding to that sID."

Things to note:

Yes, every sID will have at least one ‘Approved’ T. There may be more than one, but remember that the combination of sID + tID + Date will be unique, which means there will only be one "earliest Approved T" for that sID. [Edited to add: It is not possible for there to be 2 ‘Approved’ Ts on the same date.]
Remember that sID having SomeOtherColumn = 1 and bID = MyID in the B table is how we get to a one-to-one relationship between an sID and an INV.ID, so despite how convoluted this sounds, yes, there will always be exactly one Date per INV.ID once we get this query working. (The S table has multiple sIDs for every INV.ID, which is why we have to reference the B table first to filter down the sIDs.)

You need to sign in to view this answers

Related Post