Data from Multiple Tables

[Former Member]

I have Five Tables

1. Bill (BillId, CustomerId, BillAmount, BillDate)

2. BillItems (BillId, ItemId, Rate, Quantity)

3. AdvancePayment(BillId, AdvDate, AdvAmount)

4. RecievedAmount (BillId, RecievedAmount, RecievedDate)

5. Customer(CustomerId, CustomerName, Address etc)

I Need SQL Server Query to show daily income report in following format

Customer Name BillAmount AdvancePaid Payable PaidAmount DueAmount

1. Qasim Ali 20,000 5000 15000 10000 5000

2. Zaigham Tauqeer 25000 - 25000 20000 5000

(Due Amount is equal to Payable - Paid Amount)

I need Sql Query in this regard or any other way to access this data correctly

[Former Member · Accepted solution]

Hi Qasim,

You have five tables and you want to show the data from them.

Using crystal reports designer we can directly create the report based on these table as per the requirements.

SQL query will be automatically generated by crystal and you can see the sql generated.

These five tables have the required fields to link with each other so there wonu2019t be any problem to fetch the correct data.

Please let us know in case you want to know how to do it or I do not understand your requirement.

Regards,

Aditya Joshi

[Former Member]

Hi Quasim

You can achieve filtered data from Add Command object. Instead of selecting table choose Add Command and write the SQL query.

Hope this information will help you.

Regards

Asha.

[Former Member]

Thanks Asha

Can you make sql query please

Problem is that if there is no advance paid against billId or payment recieved it return no rows.

if there are two advance payments made in advancePayment table then it return n *2 rows. and similarly of RecievedPayments

Best Regards

Qasim Ali