Multi Table Query With Max Per Grouping

[mcfly1204]

I am looking to grab data from 3 tables, customers, contacts, and invoice. What I am looking to attain is the customer name, the primary contact name, and the oldest due date of the customer's invoices.

select distinct CUST.CustName, CUST.CustKey, CON.Name, min(INVC.DueDate)
from Customer CUST JOIN
	Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey JOIN
	Invoice INVC ON INVC.Custkey=CUST.CustKey
where INVC.Balance>.000 AND GetDate()>INVC.DueDate
group by CUST.CustKey, INVC.DueDate, CUST.CustName, CON.Name

I would imagine I would need to use subqueries, but I get thrown by needing the minimum invoice due date per customer.

**edit**

select min(duedate), INVC.CustKey, CUST.CustName, CON.Name
from Invoice INVC JOIN
	Customer CUST ON CUST.CustKey=INVC.CustKey JOIN
	Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey
where balance > .000 and INVC.companyid = 'QMC' and GetDate()>DueDate
group by INVC.CustKey, CUST.CustName, CON.Name

[ck9663]

Will this work:

select CUST.CustName, CUST.CustKey, CON.Name, min(INVC.DueDate)
from Customer CUST JOIN
    Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey JOIN
    Invoice INVC ON INVC.Custkey=CUST.CustKey
where INVC.Balance>.000 AND GetDate()>INVC.DueDate
group by CUST.CustKey, CUST.CustName, CON.Name

--- CK

[mcfly1204]

Yes, that query works too. Each query uses the same execution plan as well.