Select Query Question Please Help

[cwaldick]

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

[mendhak]

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

[Mongo]

Start by defining your most discreet dataset.

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:

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.