Wednesday, March 21, 2012

Joining to Derived Tables

How can I join to a derived table?
My derived table would look like...
(SELECT file_id,
MAX(DATETIME) AS MAXDATE
FROM DataTrac.dbo.NOTES
WHERE group_id = 'SRV'
GROUP BY file_id) MAXDTTM
and I would need to LEFT OUTER JOIN from a table called GEN matching by
file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
Let me know...
Thanks!Try,
select ...
from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
a.file_id = b.file_id
AMB
"wnfisba" wrote:

> How can I join to a derived table?
> My derived table would look like...
> (SELECT file_id,
> MAX(DATETIME) AS MAXDATE
> FROM DataTrac.dbo.NOTES
> WHERE group_id = 'SRV'
> GROUP BY file_id) MAXDTTM
> and I would need to LEFT OUTER JOIN from a table called GEN matching by
> file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
> Let me know...
> Thanks!|||Correction,
select ...
from
gen as a
left join
(select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
table
on a.file_id = b.file_id
AMB
"Alejandro Mesa" wrote:
> Try,
> select ...
> from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
> a.file_id = b.file_id
>
> AMB
> "wnfisba" wrote:
>|||Many thanks Alejandro!!!
Worked like a charm!!!
"Alejandro Mesa" wrote:
> Correction,
> select ...
> from
> gen as a
> left join
> (select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
> table
> on a.file_id = b.file_id
>
> AMB
> "Alejandro Mesa" wrote:
>

No comments:

Post a Comment