Join 3 tables with max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join 3 tables with max date

Author

Topic

unionall
Starting Member

2 Posts

Posted - 2013-12-04 : 08:51:09

Hi guys,

So I hope someone have a good idea of an effective select in this scenario;

We have 3 separate db servers with the 'same' db in a custom sync. We have a x1.people table on all servers which have a last_login column, showing the date when the user last signed in on that location.

Users travel between locations, and may have data in all the last_login columns.

So what I need is a list of users which shows the users and their last login date (regardless of location), as several have different dates on the different locations.

This is my starting point, which is wrong, as it list the one highest date of all on all the users, so if i ran it now, all users would have today as the last login date.

Any ideas? Thank you!

select
u.name,u.ID,g.group_id,
(
select max(LAST_LOGIN) from
(
Select LAST_LOGIN from CL1.DBX.X1.people
union
Select LAST_LOGIN from CL2.DBX.X1.people
union
Select LAST_LOGIN from CL3.DBX.X1.people
) as LAST_LOGIN
)as maxdate
from
X1.PEOPLE u
join X1.GROUPS g on u.GROUP = g.ID

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-12-04 : 09:02:24

how about ..

select UserID,Max(Last_Login)
FROM (
SELECT userId,Last_Login FROM Server1.SchemaName.TableName
UNION
SELECT userId,Last_Login FROM Server2.SchemaName.TableName
UNION
SELECT userId,Last_Login FROM Server3.SchemaName.TableName
) GROUP BY UserID

Cheers
MIK

unionall
Starting Member

2 Posts

Posted - 2013-12-04 : 09:27:18

Thank you MIK_2008, that did the job. Do you also know how I can throw in a 'where' specification there? Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-04 : 09:50:42

quote:
Originally posted by unionall

Thank you MIK_2008, that did the job. Do you also know how I can throw in a 'where' specification there? Regards


select UserID,Max(Last_Login)
FROM (
SELECT userId,Last_Login FROM Server1.SchemaName.TableName
WHERE <condition>
UNION 
SELECT userId,Last_Login FROM Server2.SchemaName.TableName
WHERE <condition>
UNION 
SELECT userId,Last_Login FROM Server3.SchemaName.TableName
WHERE <condition>
) GROUP BY UserID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-04 : 10:23:32

From a performance standpoint, this approach should be much better:



select UserID,Max(Last_Login)
FROM (
  SELECT userId,max(Last_Login) AS Last_Login FROM Server1.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
  UNION ALL 
  SELECT userId,max(Last_Login) AS Last_Login FROM Server2.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
  UNION ALL
  SELECT userId,max(Last_Login) AS Last_Login FROM Server3.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
) 
GROUP BY UserID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 01:47:36

quote:

Originally posted by ScottPletcher

From a performance standpoint, this approach should be much better:



select UserID,Max(Last_Login)
FROM (
  SELECT userId,max(Last_Login) AS Last_Login FROM Server1.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
  UNION ALL 
  SELECT userId,max(Last_Login) AS Last_Login FROM Server2.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
  UNION ALL
  SELECT userId,max(Last_Login) AS Last_Login FROM Server3.SchemaName.TableName
  --WHERE ...
  GROUP BY UserId
) 
GROUP BY UserID

You've commented the WHERE conditions
how will this ensure the filters get applied?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-06 : 05:47:37

quote:
Originally posted by visakh16

quote:
Originally posted by ScottPletcher

From a performance standpoint, this approach should be much better:

select UserID,Max(Last_Login)
FROM (
SELECT userId,max(Last_Login) AS Last_Login FROM Server1.SchemaName.TableName
--WHERE ...
GROUP BY UserId
UNION ALL
SELECT userId,max(Last_Login) AS Last_Login FROM Server2.SchemaName.TableName
--WHERE ...
GROUP BY UserId
UNION ALL
SELECT userId,max(Last_Login) AS Last_Login FROM Server3.SchemaName.TableName
--WHERE ...
GROUP BY UserId
)
GROUP BY UserID

You've commented the WHERE conditions
how will this ensure the filters get applied?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

By uncommenting them and coding the desired WHERE. The commented-out WHEREs indicate correct placing for the WHERE clauses. Commenting them out leaves the code runnable if an initial, proof-of-concept run w/o WHERE clauses is desired.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources