Joining Two Tables - 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

General SQL Server Forums

New to SQL Server Programming

Joining Two Tables

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-08-18 : 13:37:11

Table1

Table1ID ControlID (int)
1 100
2 101
3 102
4 103
5 104

Table2

Table2ID ControlID LastChangedDate (datetime)
1 100 08/02/2006 1:30 PM
2 100 08/16/2006 3:30 PM
3 101 08/18/2006 8:15 AM
4 102 08/14/2006 8:35 AM
5 102 08/17/2006 11:14 AM
6 103 08/18/2006 1:14 PM
7 104 08/17/2006 4:32 PM

How can you join Table1 controlID with Table2 controlID with the latest lastchangedDate or the greatest table2ID?

So my result would be

Table1ID ControlID Table2ID ControlID LastChangedDate
1 100 2 100 08/16/2006 3:30 PM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-18 : 14:13:25

-- I cannot find any logic in
>> with the latest lastchangedDate or the greatest table2ID?
and the results

Any how, u may need to modify the following


Select Table2ID , t1.ControlID ,  from Table1 t1
inner join Table2 t2 on t1.ControlID =  t1.ControlID 
where t2.lastchangedDate = (select max(lastchangedDate) from Table2)

Srinika

Norwich
Posting Yak Master

158 Posts

Posted - 2006-08-18 : 14:19:04

Not sure if this will solve your problem.
Your example is a bit vague

Select a.Table1ID, a.ControlID, Max(Table2Id) Table2Id, b.ControlID, Max(LastChangedDate)LastChangedDate
from Table1 a
Inner Join Table2 b
On a.ControlID = b.ControlID
Group By a.Table1ID, a.ControlID, b.ControlID

Regards
N

The revolution won't be televised!

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-08-18 : 14:46:29

I found it what I need is
Select <columnlist> from Table1 t1
inner join Table2 t2 on t1.ControlID = t2.ControlID
AND t2.lastchangedDate = (select max(lastchangedDate) from Table2 a
where a.controlId = t2.controlID)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 00:23:47

I think you expected result should be different than what you posted here

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources