CODEWITHSUNDEEP
mysqlvb.net
nirosha rathnayaka
nirosha rathnayaka

Reputation: 198

Merge two mysql tables and get result into datagrid in VB.NET

I have two MySQL tables,

tblloanRegistry

LoanID     EMPNumber     Date      Amount     Status
1          1111         2012-10-01  50000        0
2          2222         2012-10-10  10000        1

tblLoanAccount

ID     LoanID     Date     Payment     Interest     Total     Auto     Installment
1         1       2012-10-25  5000         0         5000        0           1
2         1       2012-11-01     0        100         100        1           0
3         1       2012-11-25  5000        100        5100        0           2
4         2       2012-11-25  1500         0         1500        0           1

Output for member 1111:

Date          Description     Principle    Interest      Balance
2012-10-25    Installment: 1     5000          0           45000
2012-11-01    Interest             0          100          45100
2012-11-25    Installment: 2     5000         100          40000

I tried the following, but it shows an error.

SELECT tblLoanAccount.Date, tblLoanAccount.Payment, tblLoanAccount.Interest, 
tblLoanAccount.Total, tblLoanAccount.Auto, tblLoanAccount.Installment FROM " &
"tblLoanAccount WHERE tblLoanAccount.EMPNumber=" & cmbEMPNumber.Text & " AND 
tblLoanAccount.LoanID = '1' AND tblLoanAccount.Total <> 0 ORDER BY tblLoanAccount.ID

Error:

enter image description here

Upvotes: 0

Views: 1386

Answers (1)

Dhinakar
Dhinakar

Reputation: 4151

This error because you haven't JOINed the tables.

Use this query for your expected answer. You should join both table then only you can get output.

SELECT tblLoanAccount.Date, tblLoanAccount.Payment, tblLoanAccount.Interest,
    tblLoanAccount.Total, tblLoanAccount.Auto, tblLoanAccount.Installment,     
    if(Installment = 0, 'Interest', concat('Installment : ', Installment)) as Description
FROM tblLoanAccount 
JOIN tblloanRegistry ON tblloanRegistry.LoanID = tblLoanAccount.LoanID
WHERE tblloanRegistry.EMPNumber= 1111 
    AND tblLoanAccount.LoanID = 1 
    AND tblLoanAccount.Total <> 0 
ORDER BY tblLoanAccount.ID

Your table schema is created at this link. Kindly go through it.

Upvotes: 1

Related Questions