x

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.
more ▼

asked Aug 24 '12 at 09:57 AM in Default

jhowe gravatar image

jhowe
1.1k 47 55 60

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Aug 24 '12 at 11:01 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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...
Sep 06 '12 at 11:47 AM jhowe
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x265

asked: Aug 24 '12 at 09:57 AM

Seen: 632 times

Last Updated: Sep 06 '12 at 11:47 AM