July 19, 2006 at 10:01 am
i have three tables that i need to join together to get a composite result set: positions, recommendations, and flags. recommendations may have multiple records that relate to one position which are correlated by position id.
when a recommendation record is added for a position, the current datetime is inserted as well. what i want to accomplish is a query that joins information from the positions table, flags table and ONLY the newest record from the recommendations table. my query looks like this and i thought this would work but it doesn't:
SELECT C.*, A.FlagID, B.Flag, B.FlagDescription, D.Target
FROM PositionFlags AS A
INNER JOIN Flags AS B
ON B.ID = A.FlagID
INNER JOIN Positions AS C
ON C.ID = A.PositionID
LEFT OUTER JOIN
(SELECT TOP 1 * FROM Recommendations AS E WHERE E.PositionID = C.ID ORDER BY E.DateAdded DESC) AS D
ON D.PositionID = C.ID
ORDER BY C.ID
when i try to execute this statement i get the following error:
The multi-part identifier "C.ID" could not be bound.
what can i do to accomplish this task? any help would be greatly appreciated.
thanks,
paul
July 19, 2006 at 10:11 am
Hi Paul,
Join Postions table with recommendations table and make a derived table. It should work.
July 20, 2006 at 2:49 am
Make the query like this:
SELECT C.*, A.FlagID, B.Flag, B.FlagDescription, D.Target
FROM PositionFlags AS A
INNER JOIN Flags AS B
ON B.ID = A.FlagID
INNER JOIN Positions AS C
ON C.ID = A.PositionID
LEFT OUTER JOIN
(SELECT * FROM Recommendations AS E WHERE E.PrimaryKeyColumn IN (SELECT DISTINCT E2.PrimaryKeyColumn FROM Recommendations AS E2 ORDER BY E2.DateAdded DESC)) AS D
ON D.PositionID = C.ID
ORDER BY C.ID
January 4, 2007 at 9:18 am
You are using SQL SERVER 2005? In this version, this error was fixed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply