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, 2012 at 09:57 AM in Default

avatar image

jhowe
1.1k 56 60 66

(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, 2012 at 11:01 AM

avatar image

Usman Butt
13.9k 6 12 21

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, 2012 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.

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:

x2070
x389

asked: Aug 24, 2012 at 09:57 AM

Seen: 978 times

Last Updated: Sep 06, 2012 at 11:47 AM

Copyright 2016 Redgate Software. Privacy Policy