April 9, 2008 at 7:29 am
Ahh... I see it.
don't reuse the alias in the subquery:
[font="Courier New"]SELECT T1.*, T2.*
FROM
Table1 T1 INNER JOIN
Table2 T2 ON
T1.FILE_NAME = T2.FILE_NAME AND
T1.Downloaded_Date = T2.Act_Date INNER JOIN
(SELECT
T1.FILE_NAME,
MAX(T1.DownLoaded_Date) AS Max_Date
FROM Table1 T1 --<<look here
GROUP BY T1.FILE_NAME
) AS T3 ON
T1.FILE_NAME = T3.FILE_NAME AND
T1.Downloaded_Date = T3.Max_Date
WHERE
T1.File_Desc = 'LEVY'[/font]
Even though you'd previously defined table1 to be aliased as T1, make the sub-query refer to the base table, not the alias. I got lazy, so I essentially redefined the T1 alias all over again in the sub-query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 7:34 am
I have run the query you provided with only one change.
In the derived table query you reference table T1 which does not exist that needs to be Table1. Here is what I am running without error:
Create Table #Table1
(
file_name varchar(100),
file_desc varchar(500),
downloaded_date datetime
)
Create Table #Table2
(
file_name varchar(100),
act_date datetime
)
Declare @i int,
@date datetime
Set @date = getdate()
Set @i = 0
While @i <= 10
Begin
Insert Into #table1
Select
'Test' + COnvert(varchar(2), @i),
'Test' + COnvert(varchar(2), @i) + ' Desc',
DateAdd(mi, -@i, @date)
Insert Into #table2
Select
'Test' + COnvert(varchar(2), @i),
DateAdd(mi, -@i, @date)
Set @i = @i + 1
End
Select
T1.*,
T2.*
From
#Table1 T1 INNER JOIN
#Table2 T2 On
T1.File_Name = T2.File_Name And
T1.Downloaded_Date = T2.Act_Date INNER JOIN
(Select
File_Name,
Max(DownLoaded_Date) as Max_Date
From
#table1
Group By
File_Name) as T3 On
T1.File_Name = T3.File_Name And
T1.Downloaded_Date = T3.Max_Date
Where
T1.File_Desc = 'Test0 Desc'
Drop Table #Table1
Drop Table #Table2
I did not generate multiple rows per file_name, but I am not getting any keyword errors
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 7:43 am
Thanks! It's working!!
Being a newbie, I have to ask this dumb question........why do I need the Group By in order to just select the maximum dates from both tables?
Couldn't it be done without it?
April 9, 2008 at 7:50 am
marge0513 (4/9/2008)
Thanks! It's working!!Being a newbie, I have to ask this dumb question........why do I need the Group By in order to just select the maximum dates from both tables?
Couldn't it be done without it?
You need the group by, since you need your logic to look at the max date by file. Meaning, on a file-by-file basis, you're looking to see stuff in table1 and table2 as of the latest download time for each file.
Taking the GROUP BY out would destroy that logic (and would essentially only show you information on the last downloaded file). If THAT is what you want, then no - you don't need the group by.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2008 at 7:54 am
Ok, I see the reasoning now.
Jack and Matt..........thanks so much for your help!!!!!
You've saved the last 3 hairs I have left on my head from all of this!
LOL!!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply