sql join one query to one row from another

Question

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.

Answer 1

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

sql join one query to one row from another

Follow this question

Related Questions