Enrique Gomez Esteban
asked on
SQL correlated join query
Hi,
I am trying the following query. Could you please give me some direction or answer?
I have 2 tables. The first one is CLIENTS with with client_number as the key and city or location. The second one is BILLING with biil_number as the key, Client_number, date_of_bill and billing_amount.
How can I get the following report with a single SQL (if possible)
Client-number Location Sum(Amount)
Of course each client can have many bills in a pre_determined period of time.
If there is no billing records for a client ZERO shoud be displayed.
I am trying the following query. Could you please give me some direction or answer?
I have 2 tables. The first one is CLIENTS with with client_number as the key and city or location. The second one is BILLING with biil_number as the key, Client_number, date_of_bill and billing_amount.
How can I get the following report with a single SQL (if possible)
Client-number Location Sum(Amount)
Of course each client can have many bills in a pre_determined period of time.
If there is no billing records for a client ZERO shoud be displayed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT client.id, client.name, client.address, client.city, SUM(billing.amount) AS amount;
FROM client, billing;
WHERE client.id = billing.clientid AND billing date >= dDateFrom AND billing date <= dDateTo;
GROUP BY client.id;
ORDER BY client.name;
INTO CURSOR query
With sqlengine>70 you will need to change group by to include all fields non aggregated.
Also changed to the given table and field names and included your demand to get 0 as sum for clients having no bills in the time period queried.
All you need to preset before running this query is two variables dDateFrom and dDateTo with the min and max date to query about:
I would suggest you also include filtering for paid bills, too, if you eg have a logical field for that or a date field like date_of_payment.
By the way this just is a simple join, no need for a correlated subquery here.
Bye, Olaf.
Also changed to the given table and field names and included your demand to get 0 as sum for clients having no bills in the time period queried.
All you need to preset before running this query is two variables dDateFrom and dDateTo with the min and max date to query about:
SELECT clients.client_number;
, clients.location;
, SUM(NVL(billing.billing_amount,00000.0000)) AS sum_amount;
FROM clients
LEFT JOIN billing on clients.client_number = billing.client_number;
AND (billing.date_of_bill Between dDateFrom AND dDateTo);
GROUP BY clients.client_number, clients.location;
ORDER BY clients.client_number;
INTO CURSOR curReport
I would suggest you also include filtering for paid bills, too, if you eg have a logical field for that or a date field like date_of_payment.
By the way this just is a simple join, no need for a correlated subquery here.
Bye, Olaf.
Open in new window