SQL Subqueries 1

[jchewsmith]

I need to create a subquery to get the MIN(INVDATE) for an order and return the MINDate in the query.

Order Table = order id, cust id
Inv table = cust id, order id and Invdate

I need my query to display all invoices for a customer and the oldest invoice for that customer

 

[bborissov]

SELECT Orders.*,
       Inv.MinDate
FROM Orders
INNER JOIN (SELECT OrderId, MIN(Invdate) AS MinDate
                  FROM Inv
             GROUP BY OrderId) Inv ON Orders.OrderId = Inv.OrderId

Borislav Borissov
VFP9 SP2, SQL Server