question

jhowe avatar image
jhowe 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.
sql-server-2008tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's exactly what i've been looking for. I'm still getting familiar with the concept of effectively joining a query back on to itself...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.