DA082 DA082 - 5 months ago 20
SQL Question

SQL Server 2008 Join, Union, Multiple Selects?

Really struggling with creating this view in SQL Server 2008. Multiple joins I have no problem getting my head around but am not quite sure of the best way to get the data I need this time.

I have one table, "versions", which has columns containing a JobNumber, Version number, DateCreated, CreatedBy, Status and description.

I would like all of these columns to be returned in the results.

Then it gets trickier.

I have another table called "Equipment". In that table I have 3 columns which I would like to cross reference with the "Versions" table. These columns are ModifiedVersion, ModifiedDate and ModifiedBy. What I need to do is find the last date a row was modified and return that in the same row as the version number from the previous table.

So the final results would be:



<table border="1">
<tr>
<td>Job Number</td>
<td>Version</td>
<td>Date Created</td>
<td>Created By</td>
<td>Last Modified</td>
<td>Last Modified By</td>
</tr>
<tr>
<td>123</td>
<td>1.00</td>
<td>01-01-2016</td>
<td>User 12</td>
<td>31-01-2016</td>
<td>User 16</td>
</tr>
<tr>
<td>123</td>
<td>2.00</td>
<td>21-03-2016</td>
<td>User 8</td>
<td>15-06-2016</td>
<td>User 11</td>
</tr>
<tr>
<td>456</td>
<td>1.00</td>
<td>01-01-2016</td>
<td>User 12</td>
<td>31-01-2016</td>
<td>User 16</td>
</tr>
<tr>
<td>456</td>
<td>2.00</td>
<td>21-03-2016</td>
<td>User 8</td>
<td>15-06-2016</td>
<td>User 11</td>
</tr>
<tr>
<td>456</td>
<td>3.00</td>
<td>01-04-2016</td>
<td>User 8</td>
<td>NULL</td>
<td>NULL</td>
</tr>
</table>





So the problem is how do I select the last modified date (easy enough using "MAX" I know) but cross reference that with the version number from the versions table.

Both the versions and equipment tables also have a "JobNumber" column which needs to have a join between them.

Finally I will then do a join on the CreatedBy and ModifiedBy columns to giv ethe actual name of the user held in a users table.

I've played around with using derived tables and various joins, I can get pretty close but get problems when different users have made modifications and if no modifications have been made. If no modifications have been made I would like the NULL values to be in the results. It's the grouping of the ModifiedBy column where it seems to go wrong.

The closest I can get is like this:

SELECT
dbo.t_ProjectEquipmentVersions.ID,
dbo.t_ProjectEquipmentVersions.JobNumber,
dbo.t_ProjectEquipmentVersions.VersionNumber,
dbo.t_ProjectEquipmentVersions.DateCreated,
dbo.t_ProjectEquipmentVersions.CreatedBy,
dbo.t_ProjectEquipment.ModifiedVersion,
MAX(dbo.t_ProjectEquipment.ModifiedDate) AS LastModifiedDate,
dbo.t_ProjectEquipment.ModifiedBy
FROM
dbo.t_ProjectEquipmentVersions
LEFT OUTER JOIN
dbo.t_ProjectEquipment ON dbo.t_ProjectEquipmentVersions.VersionNumber = dbo.t_ProjectEquipment.ModifiedVersion
GROUP BY
dbo.t_ProjectEquipmentVersions.ID,
dbo.t_ProjectEquipmentVersions.JobNumber,
dbo.t_ProjectEquipmentVersions.VersionNumber,
dbo.t_ProjectEquipmentVersions.DateCreated,
dbo.t_ProjectEquipmentVersions.CreatedBy,
dbo.t_ProjectEquipment.ModifiedVersion,
dbo.t_ProjectEquipment.ModifiedBy


This is close but I get 3 results rows instead of the 2 I would like to get. This seems to be because the modifiedBy column has different user ID's. If I remove the "ModifiedBy" from the select completely I get the results I am after.

Any advice, tips, help on this would be really appreciated.

Answer

Use OUTER APPLY

SELECT     
    pev.ID, 
    pev.JobNumber, 
    pev.VersionNumber, 
    pev.DateCreated, 
    pev.CreatedBy,     
    pe.LastModifiedDate,
    pe.ModifiedBy
FROM         
    dbo.t_ProjectEquipmentVersions  pev
OUTER APPLY (
    SELECT TOP(1) pe.ModifiedDate AS LastModifiedDate
        ,pe.ModifiedBy 
    FROM dbo.t_ProjectEquipment pe
    WHERE pe.ModifiedVersion = pev.VersionNumber 
    ORDER BY ModifiedBy DESC
) pe 
Comments