April 23, 2008 at 7:08 am
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
April 24, 2008 at 9:42 am
Hi,
I'm sorry, I'm a bit fuzzy on what you're asking for; you've asked for the most recent record only from table1 but you've then gone on to explain having two records from table1 later in your description. :crazy:
Take a look at this and see if it's close. Fire back any further suggestions or requirements (I may have missed the target totally).
-- Test Environment
DECLARE @table1 TABLE (
id SMALLINT,
date1 DATETIME
)
DECLARE @table2 TABLE (
id SMALLINT,
date2 DATETIME
)
INSERT INTO @table1 VALUES(31, '2008-01-01')
INSERT INTO @table1 VALUES(34, '2008-01-04')
INSERT INTO @table1 VALUES(31, '2008-02-03')
INSERT INTO @table2 VALUES(31, '2008-01-04')
INSERT INTO @table2 VALUES(31, '2008-04-01')
-- Solution (???)
DECLARE @table1_withID TABLE(
rowID INT IDENTITY(1, 1),
id SMALLINT,
date1 DATETIME
)
INSERT INTO @table1_withID (id, date1)
SELECT * FROM @table1 ORDER BY id, date1
DECLARE @table2_withID TABLE (
rowID INT IDENTITY(1, 1),
id SMALLINT,
date2 DATETIME
)
INSERT INTO @table2_withID (id, date2)
SELECT * FROM @table2 ORDER BY id, date2
SELECT *
FROM @table1_withID t1
INNER JOIN @table2_withID t2
ON t1.rowID = t2.rowID
April 24, 2008 at 2:53 pm
Thanks for replying, but the following query will do the trick
SELECT t2.ID
, t2.Date2
, t1.Date1
FROM Table2 AS t2
INNER
JOIN Table1 AS t1
ON T1.ID = t2.ID
AND t1.Date1 =
( SELECT MAX(Date1)
FROM Table1
WHERE ID = t2.ID
AND Date1 < t2.Date2 )
April 25, 2008 at 2:23 am
April 25, 2008 at 7:47 am
The record from table2 should always link to the record in table1 that has the same ID and table1.date1 is less then table2.date2. So to answer your question, Yes, date1 is always less than date2. Otherwise it will link to a different record or will find no record to link to, which in mine case is not possible.
April 25, 2008 at 7:50 am
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply