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.
| Author |
Topic |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2004-09-21 : 08:16:47
|
| I have two tables:User ( User_ID int, Name varchar(30) )UserEvent ( User_ID int, EventDate datetime, UserEventType_ID smallint)There are many UserEvents for every user.I want to return a resultset of every user with his last login date.Login is EventType_ID=1.How do I do this using a LEFT JOIN? |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-21 : 08:21:41
|
| select u.User_ID, max(ue.EventDate) as LastLoginfrom User u left join UserEvent ue on u.User_ID = ue.User_ID and UserEventType_ID = 1group by u.User_IDorder by u.User_IDrockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-21 : 08:24:45
|
i was supposed to post the solution but after I've scripted it i changed my mind. is this homework?better look up for joins in BOL...  |
|
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2004-09-21 : 08:37:21
|
| The query is actually much bigger, it uses many tables and returns many different types of last updated fields based on UserEventType_ID so I wanted to stay away from using group by/aggregate function.I don't spesifically want to create a subquery for each "last updated date". Is there any way to get the last date without using an aggregate function which would simplify the query? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-21 : 08:49:56
|
| Post the whole kabooza, so we can see.Can't see why you don't want to use group by and aggregate functions.You will have to at least use min or max aggregates, unless you do select top 1 ... order by ... asc/desc.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
|
|
|
Left join on Max Date