how do i get a result in 4 tables in SELECT- SQL command?

good day to the experts...

My transaction has 4 tables, 1 for recording the amount of loan, 1 for check payment, & 1 for cash payment, and the vendor table.

Below is the tables and its corresponding fields

*// vendor
table 1 (FIELDS=acct_no, acct_name,address, totalamount)

* // loan table
table 2 (FIELDS=or_number,acct_no, loanamount, or_date)

*//cash payment table

ltable 3 (FIELDS=child_numb, or_number,acct_no, amountpaid, pymntdate)

*// check payment
table 4 (FIELDS=child_numb, or_number,acct_no, checkamount, checkdate, pymntdate)

Every end of the month I wanted that the summary of loans, and its corresponding payments in checks and or cash will be sum-up and will be at totalamount field at vendor's table for each vendor. How could I change my coding into SELECT-SQL code

Thank you very much for any help regarding this matter.

Answer this question

how do i get a result in 4 tables in SELECT- SQL command?

tris111
Madix,

Why do you try to create it as a single SQL. It might be doable but it doesn't mean it'd perform better or be more readable.

create cursor table1 (acct_no i, acct_name c(10), address c(10), totalamount y)
insert into table1 (acct_no, acct_name, address, totalamount) values (1,'name1','address1',0)
*...

You could supply sample tables and data this way (and sample result you want to achieve) so message readers would have a clearer idea and send you back a tested code.
Steve_Ainsdale
With typo in code and partial I don't know where you might be going wrong. It would be better if you provided the sample data as I suggested, anyway:

select acctno, sum(amount) as cash from cashpayments group by acctno into cursor cashTotal
select acctno, sum(amount) as cheque from chequepayments group by acctno into cursor chequeTotal
select acctno, sum(amount) as loan from loans group by acctno into cursor loanTotal

select customer.*, cashTotal.cash, chequeTotal.cheque, loanTotal.loan, ;
loanTotal.loan - (cashTotal.cash + chequeTotal.cheque) as unpaid ;
from customer ;
inner join cashTotal on cashTotal.acctNo == customer.acctno ;
inner join chequeTotal on chequeTotal.acctNo == customer.acctno ;
inner join loanTotal on loanTotal.acctNo == customer.acctno
Maxon
is there any idea ...please ....

thanks

gorovvv

use the INNER JOIN...

CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT TAlias1.company, TAlias2.order_ID, ;
  TAlias3.first_name, TAlias3.last_name ;
  FROM customer AS TAlias1 ;
  INNER JOIN orders AS TAlias2 ;
   ON TAlias1.cust_id = TAlias2.cust_id ;
  INNER JOIN employee AS TAlias3 ;
   ON TAlias2.emp_id = TAlias3.emp_id ;
  ORDER BY TAlias1.company ASC

...This is the syntax

Heidi8139
so far I try that all joins based on the help file IN VFP but maybe I miss the SELECT-SQL for 4 tables that will only shown the output at the specified field in first table.

That samples in HELP only shows 3 tables in JOIN condition
Ron K.
Just want to point out that in VFP 9, you can modify Cetin's code as follows to do this in one query:

select customer.*, cashTotal.cash, chequeTotal.cheque, loanTotal.loan, ;
loanTotal.loan - (cashTotal.cash + chequeTotal.cheque) as unpaid ;
from customer ;
inner join (select acctno, sum(amount) as cash from cashpayments group by acctno) cashTotal ;
on cashTotal.acctNo == customer.acctno ;
inner join (select acctno, sum(amount) as cheque from chequepayments group by acctno) chequeTotal ;
on chequeTotal.acctNo == customer.acctno ;
inner join (select acctno, sum(amount) as loan from loans group by acctno) loanTotal ;
on loanTotal.acctNo == customer.acctno
Feras

here's how i do my code regarding this problem, i am just wondering if there is much shorter way like SELECT-SQL, i try to follow the samples in HELP files at VFP but it doesn't work. where i got wrong

sele table1
go top
SCAN
SELE table2.amount,table2.or_number from table2 WHERE table2.acct_no=table1.acct_no INTO CURSOR mycursor
IF _TALL>0
SCAN
* //code in table3 here
totalamount

* // code in table4 here
totalamount
ENDSCAN
ENDIF
replace table1. totalamount WITH totalamount
sele table1
ENDSCAN
ChuckH

thank you for your immediate reply

Here's is an example I try with your syntax:
Table1 has two vendors (vendor1 & vendor2)

Vendor2 loans an amount which is recorder in table2

Vendor2 paid in cash the loans which is recorded in table1 four times which is recorded in table3 (all-in-all 4 records at different day)

again Vendor2 paid in check which is recorder in table4 5 times at different day

your code will double the total amount at table1 if you sum it up(EX. select sum(loanamount) as loansum..... your code here....... maybe there's still missing
tikki

Thank you Tamar & Cetin, for the immediate reply.. now some of my coding looks like a work of a pro and my report output generate much faster now.

Have a nice day to all

Scott_Morrison

maybe u can use LEFT JOIN, RIGHT JOIN, or FULL JOIN... here are the differences

Inner	Retrieve only those records from tables on both sides of the join that match the join condition between fields involved in the join. Inner joins are the most common type of join.
Left (outer)	Retrieve all records from the table on the left side of the join and only those records that match the join condition from the table on the right side of the join.
Right (outer)	Retrieve only those records from the table on the left side of the join condition that match the join condition but all records from the right side of the join condition.
Full	Retrieve all records from tables on both sides of the join condition regardless of whether records match the join condition.

how do i get a result in 4 tables in SELECT- SQL command?

Answer this question