By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Multi Table Query With Max Per Grouping

100+
P: 233
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.

Expand|Select|Wrap|Line Numbers
  1. select distinct CUST.CustName, CUST.CustKey, CON.Name, min(INVC.DueDate)
  2. from Customer CUST JOIN
  3.     Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey JOIN
  4.     Invoice INVC ON INVC.Custkey=CUST.CustKey
  5. where INVC.Balance>.000 AND GetDate()>INVC.DueDate
  6. 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**

Expand|Select|Wrap|Line Numbers
  1. select min(duedate), INVC.CustKey, CUST.CustName, CON.Name
  2. from Invoice INVC JOIN
  3.     Customer CUST ON CUST.CustKey=INVC.CustKey JOIN
  4.     Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey
  5. where balance > .000 and INVC.companyid = 'QMC' and GetDate()>DueDate
  6. group by INVC.CustKey, CUST.CustName, CON.Name
Mar 26 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Will this work:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select CUST.CustName, CUST.CustKey, CON.Name, min(INVC.DueDate)
  3. from Customer CUST JOIN
  4.     Contact CON ON CON.CntctKey=CUST.PrimaryCntctKey JOIN
  5.     Invoice INVC ON INVC.Custkey=CUST.CustKey
  6. where INVC.Balance>.000 AND GetDate()>INVC.DueDate
  7. group by CUST.CustKey, CUST.CustName, CON.Name
  8.  
  9.  

--- CK
Mar 30 '09 #2

100+
P: 233
Yes, that query works too. Each query uses the same execution plan as well.
Mar 31 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.