>If I have 3 tables Invoice, InvoiceLineItems, InvoicePayments (pretty self-explanatory what each tables are for) and have to write a query to:
>
>Return all invoices (every column) that have at least 3 InvoiceLineItems and doesn't have any matching records in InvoicePayments (no payments applied yet), how would I do this?
>
>I've tried different variations of statements which none seems to work unless I type in every field in the Invoice table and use GROUP BY on same fields, I get "...because it is not contained in either an aggregate function or the GROUP BY clause" error. TIA.
Try
SELECT * FROM Invoice
WHERE ( SELECT COUNT(*) FROM InvoiceLineItems
WHERE InvoiceLineItems.InvoiceID = Invoice.Invoice_ID ) >= 3
AND NOT EXISTS (SELECT * FROM InvoicePayments
WHERE InvoicePayments.InvoiceID = Invoice.Invoice_ID )
--sb--