Select Query Question Please Help

I have 2 tables. CustomerMaster and InvoiceMaster

I need a query that gives me all the customer for whom we had more than 10 invoices for a specific period. It would be something like this.

Select * from customermaster where count (select count from invoicemaster) >10 where invoicemaster.date >= 1March 2003

Please help

Chris
:wave:

Code:

---

SELECT * FROM CustomerMaster WHERE CustomerMaster.CustomerID IN (SELECT InvoiceMaster.CustomerID from InvoiceMaster WHERE COUNT(InvoiceMaster.CustomerID) > 10)

---

Start by defining your most discreet dataset.

Code:

---

SELECT customerID
    FROM invoicemaster
    WHERE [date] >= '2003-03-01'
    GROUP BY customerID
    HAVING COUNT(*) > 10

---

Then relate, a.k.a. join, this to your customer table:

Code:

---

SELECT fieldnamelist
  FROM customermaster a
  INNER JOIN
( SELECT customerID
    FROM invoicemaster
    WHERE [date] >= '2003-03-01'
    GROUP BY customerID
    HAVING COUNT(*) > 10
 ) AS c (customerID)
  ON c.customerID = a.customerID

---

The correct SQL for your specific JOIN will differ if you
are not using MS SQL Server, but this is the concept I
suggest you explore. HTH.