asked
sql join one query to one row from another
Hi all i'm going to try and ask this the best way i can because it confuses the hell out of me. Example : 4 tables CREATE TABLE Company ( CompanyID INT , NAME NVARCHAR ) CREATE TABLE CompanyProfile ( ProfileID INT , CompanyID INT FOREIGN KEY REFERENCES Company , CompanyInfo NVARCHAR ) CREATE TABLE Execution ( ExecutionID INT , CompanyID INT FOREIGN KEY REFERENCES Company , DataFileID INT ) CREATE TABLE CompanyDataFile ( DataFileID INT , FileInfo NVARCHAR ) Now what i want to do is select all the companys with profiles so inner join between company and company profile, but then the tricky bit is i want to join that with the most recent file processed for EACH company which is in the companydatafile table, using the execution table to link between the three. How can i do this??? i.e each company has many files etc.
Sorry, without some test data, expected output and assuming the highest executionid corresponds to most recent file processed, this is kind of a pseudo-code to get you started DECLARE @Company TABLE ( CompanyID INT , NAME NVARCHAR ) DECLARE @CompanyProfile TABLE ( ProfileID INT , CompanyID INT , CompanyInfo NVARCHAR ) DECLARE @Execution TABLE ( ExecutionID INT , CompanyID INT , DataFileID INT ) DECLARE @CompanyDataFile TABLE ( DataFileID INT , FileInfo NVARCHAR ) ;WITH CTE AS ( SELECT CDF.*, [Ex].CompanyID, ROW_NUMBER() OVER (PARTITION BY [CompanyID] ORDER BY [ExecutionID] DESC) RowNum FROM @Execution Ex JOIN @CompanyDataFile AS CDF ON Ex.[DataFileID] = CDF.[DataFileID] ) SELECT * FROM @Company AS C JOIN @CompanyProfile AS CP ON [C].[CompanyID] = [CP].[CompanyID] LEFT JOIN [CTE] --LEFT JOIN USED IF ALL THE COMAPNIES NEEDED ON [C].[CompanyID] = [CTE].[CompanyID] AND [RowNum] = 1 -- THE HIGHEST ExecutionId
8 People are following this question.
