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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -