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:
>

Join using max value

I want to join two tables together but only join the where the column
in table 2 (of type date) is the maximum value. Is this possible?

Note table 1 is the main table it is also joined to other tables too.Hello,

Would something like this work?

select
*
from
table1 t1
join table2 t2 on t1.joinkey=t2.joinkey
where
t2.date = (select max(datefield) as max_date from table2)

Greg.

ree32 wrote:
> I want to join two tables together but only join the where the column
> in table 2 (of type date) is the maximum value. Is this possible?
> Note table 1 is the main table it is also joined to other tables too.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is a bitch to debug code that you cannot see.|||Actually, if you read his request, he is only asking if its possible.

So a simple YES, (sans any explanation of technique), would appear to
suffice.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112319257.363540.54270@.f14g2000cwb.googlegro ups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications. It is a bitch to debug code that you cannot see.|||Thanks for the idea, I will test it out later.

"Greg" <gregdenison@.gmail.com> wrote in message news:<1112314603.380779.78130@.g14g2000cwa.googlegroups.c om>...
> Hello,
> Would something like this work?
> select
> *
> from
> table1 t1
> join table2 t2 on t1.joinkey=t2.joinkey
> where
> t2.date = (select max(datefield) as max_date from table2)

Join two tables using sum and max

I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.

Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).

My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.

Can someone please cast some pearls my way ?

ThanksThis may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
GO

INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
SELECT '1/1/2005',1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1 UNION ALL
SELECT '1/1/2005',1,3,1 UNION ALL
SELECT '1/1/2005',1,4,1

INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
SELECT '1/1/2005',1,1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1,1 UNION ALL
SELECT '1/1/2005',1,3,1,1 UNION ALL
SELECT '1/1/2005',1,1,1,2 UNION ALL
SELECT '1/1/2005',1,2,1,2 UNION ALL
SELECT '1/1/2005',1,3,1,2 UNION ALL
SELECT '1/1/2005',1,4,1,1
GO

SELECT *
FROM (
SELECT Dealid, SUM(Nominal) AS SUM_Nominal
FROM myTable99
GROUP BY Dealid) AS xxx
JOIN ( SELECT *
FROM myTable00 a
WHERE DealSeq = (SELECT MAX(Dealseq)
FROM myTable00 b
WHERE a.Dealid = b.Dealid)) AS yyy
ON xxx.Dealid = yyy.Dealid

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO