join two table duplicate fields

[enigmagirl122]

hi


here is my 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!

[Hack]

Moved To Database Development

[NeedSomeAnswers]

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 ?

[nubie]

try the RIGHT JOIN instead of INNER JOIN

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

[si_the_geek]

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:

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:

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:

  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.

[si_the_geek]

Duplicate threads merged - please post each question (or variation of it) only once.

[enigmagirl122]

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!

[NeedSomeAnswers]

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 !

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.

[nubie]

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

[NeedSomeAnswers]

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.

[GaryMazzone]

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.

[si_the_geek]

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.

[NeedSomeAnswers]

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

[si_the_geek]

So if i say Left Join is that not the same as saying Left Outer Join ?

They are the same.

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.

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.