asked on

Inner join on a Select statement

I have two tables that I am trying to join, but I need to perform a MAX function against the second table before they are joined.

CREATE TABLE [dbo].[GroupPolicyObjects] (
      [gpoID] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DisplayName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [CreationTime] [datetime] NOT NULL ,
      [ModificationTime] [datetime] NOT NULL ,
      [Owner] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [IsUserEnabled] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [IsComputerEnabled] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [UserDSVersionNumber] [int] NOT NULL ,
      [UserSysvolVersionNumber] [int] NOT NULL ,
      [ComputerDSVersionNumber] [int] NOT NULL ,
      [ComputerSysvolVersionNumber] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[GPODescriptions] (
      [pkAuto] [int] IDENTITY (1, 1) NOT NULL ,
      [gpoID] [varchar] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [gpoDescription] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [editDate] [datetime] NOT NULL ,
      [editByUser] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

"SElect ID,MAX(EditDate) as Date from GPODescriptions Group BY ID" returns the correct set of rows from the second table, but I can't figure out how to join this with results from GroupPolicyObjects. GPODescriptions will contain history of descriptions, so there will be multiple records for any gpoID, I just want to return the most recent, based on editDate.

This query:
SELECT GroupPolicyObjects.gpoID,
GroupPolicyObjects.DisplayName,
GroupPolicyObjects.CreationTime,
GroupPolicyObjects.ModificationTime,
GroupPolicyObjects.UserDSVersionNumber,
GroupPolicyObjects.UserSysvolVersionNumber,
GroupPolicyObjects.ComputerDSVersionNumber,
GroupPolicyObjects.ComputerSysvolVersionNumber,
GPODescriptions.gpoDescription,
GPODescriptions.EditByUser,
GPODescriptions.EditDate
FROM GroupPolicyObjects
LEFT OUTER JOIN GPODescriptions
ON GroupPolicyObjects.gpoID = GPODescriptions.gpoID
ORDER BY DisplayName ASC

Returns the right set of columns, with proper joining, but I get an entry for each description when I just want the latest.

ASKER CERTIFIED SOLUTION

mastoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

benhanson

ASKER

Funny that you mis-typed a few of the gpoID references, since those columns were previously just ID. Otherwise, I think you've got it! I'm going to test this in the web interface it is destined for, then accept your comment after testing. Thanks for the quick answer, my brain was preztelized . . . Final query(just changed ID to gpoID in a few spots:

SELECT GroupPolicyObjects.gpoID,
GroupPolicyObjects.DisplayName,
GroupPolicyObjects.CreationTime,
GroupPolicyObjects.ModificationTime,
GroupPolicyObjects.UserDSVersionNumber,
GroupPolicyObjects.UserSysvolVersionNumber,
GroupPolicyObjects.ComputerDSVersionNumber,
GroupPolicyObjects.ComputerSysvolVersionNumber,
GPODescriptions.gpoDescription,
GPODescriptions.EditByUser,
GPODescriptions.EditDate
FROM GroupPolicyObjects
LEFT OUTER JOIN ( ( Select gpoID,MAX(EditDate) as MaxDate
from GPODescriptions
Group BY gpoID ) As MyMax
Join GPODescriptions
On MyMax.gpoID = GPODescriptions.gpoID
And MyMax.MaxDate = GPODescriptions.EditDate)
ON GroupPolicyObjects.gpoID = GPODescriptions.gpoID
ORDER BY DisplayName ASC

benhanson

ASKER

That did the trick, thank you very much for your help!

mastoo

Glad to help. Sorry for the typos.