SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Join 3 tables with max date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

unionall
Starting Member

2 Posts

Posted - 12/04/2013 :  08:51:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 12/04/2013 :  09:02:24  Show Profile  Reply with Quote

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 - 12/04/2013 :  09:27:18  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/04/2013 :  09:50:42  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
421 Posts

Posted - 12/04/2013 :  10:23:32  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/05/2013 :  01:47:36  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
421 Posts

Posted - 12/06/2013 :  05:47:37  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000