join two table duplicate fields
hi :wave:
here is my code:
Code:
'payloans and advance payment
prcconnection
If rs.State = adStateOpen Then rs.Close
rs.Open "select tblMainLoansPayment.Loans_Info_Id,tblMainLoansPayment.Id_No,tblMainLoansPayment.FullName,tblMainLoansPayment.LOAN_TYPE,tblMainLoansPayment.LOAN_AMOUNT,tblMainLoansPayment.PAYROLL_DATE,tblMainLoansPayment.AMORTIZATION,tblMainLoansPayment.AMORTIZATION2,tblMainLoansPayment.Approved_date,tblLoanAdvancePayment.loan_info_id,tblLoanAdvancePayment.adv_ORDate,tblLoanAdvancePayment.adv_Amount from tblMainLoansPayment inner join tblLoanAdvancePayment on tblMainLoansPayment.Loans_Info_Id=tblLoanAdvancePayment.loan_info_id where tblMainLoansPayment.Loans_Info_Id= " & lstNameList.ItemData(lstNameList.ListIndex) & " and tblLoanAdvancePayment.loan_info_id=" & lstNameList.ItemData(lstNameList.ListIndex) & "", con, adOpenStatic, adLockOptimistic
If rs.RecordCount >= 1 Then
Call cleanloanstatgrid
Label7.Caption = rs!Loans_Info_Id
If lstNameList.ListIndex = -1 Then Exit Sub
rs.MoveFirst
X = 1
MSHFlexGrid1.Rows = rs.RecordCount + 1
Do While Not rs.EOF
With MSHFlexGrid1
txtname.Text = rs!FullName
txtID.Text = rs!ID_NO
txttype.Text = rs!LOAN_TYPE
txtloanamount.Text = FormatNumber(rs!LOAN_AMOUNT, 2)
TxtAmortization.Text = FormatNumber(rs!AMORTIZATION, 2)
txtAmortization2.Text = FormatNumber(rs!AMORTIZATION2, 2)
TxtApprovedDate.Text = rs!APPROVED_DATE
.TextMatrix(X, 1) = rs!PAYROLL_DATE
If rs!AMORTIZATION2 = 0 Then
.TextMatrix(X, 2) = FormatNumber(rs!AMORTIZATION, 2)
ElseIf rs!AMORTIZATION2 >= 1 Then
.TextMatrix(X, 2) = FormatNumber(rs!AMORTIZATION2, 2)
End If
If rs!AMORTIZATION = 0 And rs!AMORTIZATION2 = 0 Then
.TextMatrix(X, 2) = "0.00"
End If
.TextMatrix(X, 3) = rs!adv_ORDate
If rs!adv_Amount = 0 Then
.TextMatrix(X, 4) = "0.00"
ElseIf rs!adv_Amount >= 1 Then
.TextMatrix(X, 4) = FormatNumber(rs!adv_Amount, 2)
End If
X = X + 1
End With
rs.MoveNext
Loop
Label63.Caption = "Found " & rs.RecordCount & " records"
con.Close
CmdPrint.Enabled = True
Call totalloan
Call deductloan
Exit Sub
End If
that code is not working properly. I'm joining 2 tables with same loan_id.there are instances where table1 has 2 records while table2 has 1 record only.what happens it the table shows records 1 and 2 from table1 while table2 display record 1 twice. where it should be like this.
table 1 table2
record1 record1
record2 <empty>
i inserted an condition to no avail. pls help what is missing in my code.
thanks!
Re: joining 2 tables without duplicates
Moved To Database Development
Re: joining 2 tables without duplicates
I think your problem is you are joining your tables using the LoanID field and LoanID is not Unique.
Do your records have any other unique identifier like an ID field ?
Re: joining 2 tables without duplicates
try the RIGHT JOIN instead of INNER JOIN
Code:
select tblMainLoansPayment.Loans_Info_Id,
tblMainLoansPayment.Id_No,
tblMainLoansPayment.FullName,
tblLoanAdvancePayment.adv_Amount
from tblMainLoansPayment right join
tblLoanAdvancePayment on
tblMainLoansPayment.Loans_Info_Id=tblLoanAdvancePayment.loan_info_id
Re: join two table duplicate fields
One thing you are seriously missing is readability - the way you have written the rs.Open line makes it extremely hard to read and debug:
Code:
rs.Open "select tblMainLoansPayment.Loans_Info_Id,tblMainLoansPayment.Id_No,tblMainLoansPayment.FullName,tblMainLoansPayment.LOAN_TYPE,tblMainLoansPayment.LOAN_AMOUNT,tblMainLoansPayment.PAYROLL_DATE,tblMainLoansPayment.AMORTIZATION,tblMainLoansPayment.AMORTIZATION2,tblMainLoansPayment.Approved_date,tblLoanAdvancePayment.loan_info_id,tblLoanAdvancePayment.adv_ORDate,tblLoanAdvancePayment.adv_Amount from tblMainLoansPayment inner join tblLoanAdvancePayment on tblMainLoansPayment.Loans_Info_Id=tblLoanAdvancePayment.loan_info_id where tblMainLoansPayment.Loans_Info_Id= " & lstNameList.ItemData(lstNameList.ListIndex) & " and tblLoanAdvancePayment.loan_info_id=" & lstNameList.ItemData(lstNameList.ListIndex) & "", con, adOpenStatic, adLockOptimistic
I would strongly recommend using a variable to build the SQL statement (using multiple lines as apt), and then using the variable on the rs.Open line, like this:
Code:
Dim strSQL as String
strSQL = "select tblMainLoansPayment.Loans_Info_Id,tblMainLoansPayment.Id_No, " _
& "tblMainLoansPayment.FullName,tblMainLoansPayment.LOAN_TYPE," _
& "tblMainLoansPayment.LOAN_AMOUNT,tblMainLoansPayment.PAYROLL_DATE," _
& "tblMainLoansPayment.AMORTIZATION,tblMainLoansPayment.AMORTIZATION2," _
& "tblMainLoansPayment.Approved_date,tblLoanAdvancePayment.loan_info_id," _
& "tblLoanAdvancePayment.adv_ORDate,tblLoanAdvancePayment.adv_Amount" _
& "from tblMainLoansPayment " _
& "inner join tblLoanAdvancePayment on tblMainLoansPayment.Loans_Info_Id=tblLoanAdvancePayment.loan_info_id " _
& "where tblMainLoansPayment.Loans_Info_Id= " & lstNameList.ItemData(lstNameList.ListIndex) _
& " and tblLoanAdvancePayment.loan_info_id=" & lstNameList.ItemData(lstNameList.ListIndex)
rs.Open strSQL, con, adOpenStatic, adLockOptimistic
From this it is fairly easy to see that the last line of the SQL statement (the "and...") is not needed, because it repeats the work that is done in the previous two lines.
If you also use table aliases within your SQL statement, you can make it much shorter and even easier to read:
Code:
strSQL = "select MLP.Loans_Info_Id, MLP.Id_No, MLP.FullName, MLP.LOAN_TYPE," _
& "MLP.LOAN_AMOUNT, MLP.PAYROLL_DATE, MLP.AMORTIZATION, MLP.AMORTIZATION2," _
& "MLP.Approved_date, LAP.loan_info_id, LAP.adv_ORDate, LAP.adv_Amount" _
& "from tblMainLoansPayment as MLP " _
& "inner join tblLoanAdvancePayment as LAP on MLP.Loans_Info_Id=LAP.loan_info_id " _
& "where MLP.Loans_Info_Id= " & lstNameList.ItemData(lstNameList.ListIndex)
As to your question, the behaviour you are seeing is exactly what you should be expecting, because that is how joins work (each record from one table is shown with all records from the other table that relate to it).
If you want it to be displayed differently, change your VB code to display it the way you want.
Re: join two table duplicate fields
Duplicate threads merged - please post each question (or variation of it) only once.
Re: join two table duplicate fields
thanks everyone,
but how can i display it the way i want.the only field that connects them is the loan_id.and its not a unique.
Thanks!
Re: join two table duplicate fields
Quote:
try the RIGHT JOIN instead of INNER JOIN
They are the same thing, if you just put 'Inner Join', with out 'right' or 'Left' it defaults to right. In fact i never understand the syntax in SQL Server of 'Right Inner' and 'Left Outer', there is no need for the Inner and Outer keywords as they basically mean the same thing as right and left !
Quote:
but how can i display it the way i want.the only field that connects them is the loan_id.and its not a unique.
Right that is a problem, and not good database design. Foreign Keys should be unique for this very reason.
If you had used the Primary key of tblMainLoansPayment as a ForiegnKey in tblLoanAdvancePayment then you would not be having this problem now.
I am not sure how you would get the results from a query. If you are using SQL Server you could use a Stored Procedure to build the result you want.
Re: join two table duplicate fields
Quote:
Originally Posted by
NeedSomeAnswers
They are the same thing, if you just put 'Inner Join', with out 'right' or 'Left' it defaults to right. In fact i never understand the syntax in SQL Server of 'Right Inner' and 'Left Outer', there is no need for the Inner and Outer keywords as they basically mean the same thing as right and left !
perhaps i misunderstood this http://msdn.microsoft.com/en-us/libr...y4(VS.80).aspx
Inner join A join that displays only the rows that have a match in both joined tables.
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table.
Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join
Re: join two table duplicate fields
Inner Join / right Inner Join,right Join are the same thing.
Outer Join / right Outer Join are the same thing.
Left Outer Join / Left Join are the same thing.
Re: join two table duplicate fields
Inner join is not the same as Right Join. Join is the same as saying Inner Join. That is completepy different then a Right Outer Join. The Outer is necessary in both the Left, Right and Full Outer joins.
Re: join two table duplicate fields
NeedSomeAnswers, you have it very wrong I'm afraid - what nubie posted is correct.
Right and Left are two variations of Outer. The keyword Outer can be optional (depending on the database system), sometimes Left is too (but Outer must be used instead).
The keywords Right and Left are completely meaningless for an Inner join. The keyword Inner is sometimes optional.
enigmagirl122, I recommend that you show us a little sample of the data that isn't being displayed as you want - including what is shown, the data in the tables that it is created from, and what you want to be shown instead.
Re: join two table duplicate fields
Quote:
NeedSomeAnswers, you have it very wrong I'm afraid - what nubie posted is correct.
As far as i can see i made 1 small mistake lumping right join next to inner join !
So if i say Left Join is that not the same as saying Left Outer Join ?
And if i say Right Inner Join is that not the same as saying Inner Join ?
What i was trying to point out is in cases like these there seems to be unnecessary extra keywords.
As far as i can see it would be simpler if we had;
Join or Inner Join
&
Left & right join
and leave it at that
I have to say i cant imagine a scenario where i would need to use a Full Join and were it would bring back any different results than i could get using a where clause
Re: join two table duplicate fields
Quote:
Originally Posted by
NeedSomeAnswers
So if i say Left Join is that not the same as saying Left Outer Join ?
They are the same.
Quote:
And if i say Right Inner Join is that not the same as saying Inner Join ?
No, because there is no Right (or Left) Inner Join.
Right Join is a Right Outer Join.
The difference between Left and Right is the table that gets treated as "always show this one". In basic terms, "A Left Join B" is the same as "B Right Join A" (but there are rare cases where you need one rather than the other).
A Full (Outer) Join shows all the rows from both tables, even when there is no match.
The joins that are usually supported are:
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
the words in square brackets might be optional, depending on the database system.
Quote:
What i was trying to point out is in cases like these there seems to be unnecessary extra keywords. ...
I partly agree, but the extra words add clarity which can be nice - and using them may have avoided your confusion.
