Multiple selects

[Hiche]

Hi,
i have created a queries with multiple selects in the sql, for example:

select trx_date, client_no, sale_amount,
(select sum(payment_amount) from receipt where trx_date=trx_date) as payments
from sales.



my problem is that the queries in returning payments on date of sales and not returning data if on a date there is a payment but no sales.

How can i solve this issue.

Thank you in advance

[CJ_London]

sounds like you are not approaching this the right way.

think you need a union query of all dates

QryU

SELECT trx_date, client_no FROM Sales
UNION SELECT trx_date, client_no FROM Receipt

then a aggregate query for each of sales and receipt

QrySA

SELECT trx_date, client_no, sum(Sale_Amount) as sumSales
FROM Sales
GROUP BY trx_date, client_no

QryRA

SELECT trx_date, client_no, sum(Payment_Amount) as sumReceipts
FROM Receipt
GROUP BY trx_date, client_no

then join together with a left join

SELECT QryU.trx_date, QryU.client_no, sumSales, sumReceipts
FROM (QryU LEFT JOIN QrySA ON  QryU.trx_date=QrySA.trx_date AND QryU.client_no=QrySA.client_no) LEFT JOIN QryRA ON  QryU.trx_date=QryRA.trx_date AND QryU.client_no=QryRA.client_no