Outer join table where there is 2 foreign keys to join

Question

Post reply

Outer join table where there is 2 foreign keys to join

mjohnson 71479

SSC Eights!

Points: 846
More actions
March 29, 2024 at 5:16 pm
Go to Answer
#4404517
I am asked to provide a date in a query from a table that may or may not have a record. Thus the outer join. The issue is the joined table may have many records for the ID (foreign key) being joined, and the query is returning the first result not the last. There is an additional foreign key that each record would have in them. how would I do that join?
Here is the current query.
```
SELECT 
A.UserID, 
A.Completed, 
A.FolderID, 
ISNULL(A.AppDateTimeEnd,AppDateTimeStart) AS AppDate, 
A.ResumeFileName, 
A.FirstName, 
A.LastName, 
A.PrescreenScore, 
A.JobID, 
A.ViewPre, 
A.ViewApp, 
A.ViewReport, 
A.ViewResume, 
A.MentalAcuity,
A.Score,
J.JobTitle,
J.TestType,
L.AreaName,
L.State 
FROM 
dbo.Jobs J INNER JOIN 
dbo.Application A ON J.JobID = A.JobID INNER JOIN (
SELECT UserID,MIN(LocationID) AS LocationID
FROM dbo.Application_Locations 
       GROUP BY UserID ) AL ON AL.UserID = A.UserID INNER JOIN 
dbo.Locations L ON AL.LocationID = L.LocationID
```
I want to add the matching disposition date for the UserID when it is the same FolderID as in the Application table
Here is what I currently have for a query where it is returning the wrong record's date.
```
SELECT 
A.UserID, 
A.Completed, 
A.FolderID, 
ISNULL(A.AppDateTimeEnd,AppDateTimeStart) AS AppDate, 
A.ResumeFileName, 
A.FirstName, 
A.LastName, 
A.PrescreenScore, 
A.JobID, 
A.ViewPre, 
A.ViewApp, 
A.ViewReport, 
A.ViewResume, 
A.MentalAcuity,
A.Score,
J.JobTitle,
J.TestType,
L.AreaName,
L.State,
D.DispositionDate
FROM 
dbo.Jobs J INNER JOIN 
dbo.Application A ON J.JobID = A.JobID INNER JOIN (
SELECT UserID,MIN(LocationID) AS LocationID
FROM dbo.Application_Locations 
       GROUP BY UserID ) AL ON AL.UserID = A.UserID INNER JOIN 
dbo.Locations L ON AL.LocationID = L.LocationID LEFT OUTER JOIN
        dbo.Applicant_Disposition D ON A.UserID = D.UserID
```
There are times when there is no record in the disposition table. For example when the applicant has not completed the application or when Manager/HR have not viewed or made a decision on the application. When they do decide, they move the application through the hiring process, so there will be a record each time the applicant is moved forward until either hired or they are rejected. Either way a record with the FolderID in the Applicant_Disposition table will match what shows in the Application table and that is the DispositionDate that I want.
ScottPletcher

SSC Guru

Points: 100949
More actions
March 29, 2024 at 5:23 pm
Answer
#4404518
Something like this:
```
FROM
...
dbo.Locations L ON AL.LocationID = L.LocationID OUTER APPLY (
    SELECT TOP (1) D.*
    FROM dbo.Applicant_Disposition D
    WHERE A.UserID = D.UserID
    ORDER BY ID DESC
) AS D
```
- This reply was modified 1 month ago by ScottPletcher. Reason: Added "TOP (1)" to outer apply query that I forgot to put in earlier, D'OH
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 1

mjohnson 71479

SSC Eights!

Points: 846

March 29, 2024 at 6:10 pm

#4404542

Thank You!!!

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

You're welcome! I had to make one correction to the query above, btw.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.