--- In firebird-support@yahoogroups.com, "tickerboo2002" <egroup@...>
wrote:

>
> Suppose I have the following tables:
>
> Clients
> Client_ID
> Address1
> Address2
> etc
>
> Invoices_Outstanding
> InvoicesOutstanding_ID
> Client_ID
> Amount
>
> Invoices_paid
> InvoicesPaid_ID
> Client_ID
> Amount
> Date_Paid
>
>
> With one SQL query, is it possible to retrieve a row from Clients
> (PK=CLIENT_ID) and also a count of the number of entries in both
> Invoices_Outstanding and Invoices_Paid? Something like:
>
> Client_ID,
> Address1,
> Address2,
> CountOfInvoicesOutstanding,
> CountOfInvoicesPaid.
>
> Thanks.
>

select CL.*, (select count(client_id) from Invoices_paid INVPAID where
INVPAID.client_id = CL.client_id) AS "CountOfPaid", (select
count(client_id) from Invoices_Outstanding INVOS where INVOS.client_id
= CL.client_id) AS "CountOfOutstanding" from Clients CL where
CL.client_id=:client_id