Steve,
Local SQL will do
SELECT O.CustNo, O.EmpNo, O.SaleDate, C.*
FROM Customer C LEFT OUTER JOIN Orders.db O ON C.CustNo = O.CustNo
WHERE O.SaleDate = (SELECT MAX(Ord.SaleDate) FROM Orders.db Ord
WHERE Ord.CustNo = O.CustNo AND Ord.EmpNo = O.EmpNo)
GROUP BY O.Custno, O.EmpNo, O.SaleDate, C.CustNo, C.Company, C.Addr1, C.Addr2,
C.City, C.State, C.Zip, C.Country, C.Phone, C.Fax, C.TaxRate, C.Contact,
C.LastinvoiceDate
I think it will give you the right result but I wouldn't want to wait for it on a slow
machine because the subquery is dependent on the outer query (it took about 2 seconds
for
the above to return on my development PC - PII-266 orders has 205 records, customer 55
-
DBDEMOS tables).
Max
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Stephen Barker
Sent: Tuesday, 23 February 1999 16:17
To: Multiple recipients of list delphi
Subject: [DUG]: joining query to a table
Hi,
is it possible to define sql to join a table with a query?
e.g. what I would like to be able to do is:
select * from dealers d left join <transquery> q on d.dealerid=q.dealerid
where <transquery> is a query as follows:
select t.dealerid, t.branchcode,max(t."date") from "trans.db" t
group by t.dealerid, t.branchcode
i.e. what I could do is create a new field on dealers and populate it with
the latest transaction date from the trans table, but I would rather do it
all from sql and not change the table structures.
btw, this is using bde and paradox tables.
thanks,
Steve.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
