Problem with FULL OUTER JOIN

dlarkin77

Hi, I have two tables as follows:

CREATE TABLE [dbo].[WebSyncHistory](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncHistory_SyncTableName] DEFAULT (''),
[SyncTime] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[WebSyncTimesMS](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncTimesMS_SyncTableName] DEFAULT (''),
[LastActionTime] [datetime] NOT NULL
) ON [PRIMARY]

I have some records in each of these tables:

INSERT INTO WebSyncHistory VALUES (1, 'Products', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (1, 'Categories', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (2, 'DeviceSettings', '01/01/2008')

INSERT INTO WebSyncTimesMS VALUES (1, 'Products', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'DeviceSettings', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'Categories', '01/01/2008')

Each table has two fields with the same name and data type What I want to do is select all records from each table in such a way that I end up with 4 columns - UserID, SyncTableName, SyncTime, LastActionTime

UserID SyncTableName SyncTime LastActionTime
1 Products 01/01/2008 31/08/2008
1 Categories 01/01/2008 NULL
1 DeviceSettings NULL 01/01/2008
2 DeviceSettings 01/01/2008 31/08/2008
2 Categories NULL 01/01/2008

The closest I can get to what I want is the following:

SELECT [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,MAX([WebSyncHistory].[SyncTime]) As SyncTime
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]
FROM ([WebSyncHistory] FULL OUTER JOIN [WebSyncTimesMS]
ON [WebSyncHistory].[UserID] = [WebSyncTimesMS].[UserID] AND [WebSyncHistory].[SyncTableName] = [WebSyncTimesMS].[SyncTableName])

GROUP BY [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]

which returns:

UserID SyncTableName SyncTime UserID SyncTableName LastActionTime
NULL NULL NULL 1 DeviceSettings 2008-01-01 00:00:00.000
NULL NULL NULL 2 Categories 2008-01-01 00:00:00.000
1 Categories 2008-01-01 00:00:00.000 NULL NULL NULL
1 Products 2008-01-01 00:00:00.000 1 Products 2008-08-31 00:00:00.000
2 DeviceSettings 2008-01-01 00:00:

Syed Mehroz Alam

Looking at the output you want, I dont think you need a "full" outer join since you always want to match UserID and SyncTableName. Here's a solution. I will use a two-step procedure to solve this: 1. Get unique UserID and SyncTableNames (Creating a CTE will be best here) 2. Have a left outer join with the two tables

--create a CTE
With UserTableMods( [UserID], [SyncTableName])
As
(
Select distinct [UserID], [SyncTableName] from [WebSyncTimesMS]
union
Select distinct [UserID], [SyncTableName] from [WebSyncHistory]
)

--Get result
Select UTM.[UserID], UTM.[SyncTableName], H.[SyncTime], M.[LastActionTime]
From UserTableMods UTM
LEFT OUTER JOIN [WebSyncHistory] H ON UTM.USERID=H.USERID and UTM.[SyncTableName]=H.[SyncTableName]
LEFT OUTER JOIN [WebSyncTimesMS] M ON UTM.USERID=M.USERID and UTM.[SyncTableName]=M.[SyncTableName]

And here's the result:

1 Categories 2008-01-01 NULL
1 DeviceSettings NULL 2008-01-01
1 Products 2008-01-01 2008-08-31
2 Categories NULL 2008-01-01
2 DeviceSettings 2008-01-01 2008-08-31

You can use the Grouping and Max functions as per your requirement. Hope that helps. Regards, Syed Mehroz Alam

My Blog My Articles

dlarkin77

That works perfectly. Thanks very much Syed

Syed Mehroz Alam

Glad to help you.

My Blog My Articles