MS SQL join complex subquery
Posted on 2013-05-13
I am trying to combine my query into a join to bring back some records.
Here is the first part of my query, this does exactly what I want and returns the latest record from this result set as each one is linked to the other as show below.
This query returns 90616 from the results.
DECLARE @LatestSDS INT
with tree as (
SELECT pkSDSID, fkSDSID
WHERE pkSDSID = 50605
SELECT t1.pkSDSID, t1.fkSDSID
FROM tblSDS t1
JOIN tree p ON p.pkSDSID = t1.fkSDSID
SELECT TOP 1 @LatestSDS = pkSDSID FROM tree ORDER BY pkSDSID DESC
This is fine if I have one record I want to find but what I'm now stuck on his how to do this for multiple records.
I was wondering if I could inner join this somehow using it as a sub query of my main query but I cant seem to find a way to get it to work.
What I would like is to use the pkSDSID like above which is 50605 but use a column of a table and get the LastestSDS for each record.
I hope this makes sense I'm not all that great with SQL and I just don't know where to go from here.
Is this possible?
I appreciate all help. Thank you.