Sql - joining 4 tables

See more:

VB

SQL

I apologize for my opening statement but it must be said... I am about to ask a question that may seem simple to the experienced users. Please do not belittle me by saying something should be obvious to me or you "will not write the code for me" as this is NOT what I am requesting. I am so tired of being treated like an imbecile by snobs when I ask a question. A simple helpful hand and direction pointing is all I am asking, not a code rewrite, so I may understand for the future and not be asked to be helped again on this issue.

I am attempting to join into another table to write data. The JOIN I have currently works fine, but when I add another JOIN statement I am getting lost. My skill set is limited when it comes to multiple joins and this joining is pretty involved.

I have 2 tables, tbSECTIONS and tbDESCRIPT that hold "common data" which is nothing more than a Table of section names, assigned a number and, likewise, the tbDESCRIPT table of Descriptions assigned a number and which are joined and referred to by the tbEXPENSES table. This works fine.

tbEXPENSES is a table contains the expense information and that uses the above tables to categorize the items based on section and description numbers assigned to the inputted data and writes it to the Weekly_Expense_Table. THIS works fine.

I now need to now separate what is not written to the Weekly_Expense_Table and put that information into the the PersonalExpenses table. I have attempted to join the PersonalExpenses table via multiple places and multiple ways but nothing I am doing is correct. My lack of knowledge lies on what table should I join to? The only thing that could be in common would be the tbDESCRIPT table matching column names in the PersonalTable. I am sure I am not explaining this well, due to my lack of understanding but will add ANY code that may assist someone in deciphering my issue. Just simply ask what piece(s) you need of the code.

My area of issue is the line:

LEFT OUTER JOIN PersonalExpenses ON tbEXPENSES.REC_DATE = PersonalExpenses.RECEIPT_DATE" 

It obviously is not joining the table properly as I get the "Key not found" error when I try to run the query.

What I have tried:

My SQL JOIN currently is coded as such:

Dim begdate As String = wrkdate1.ToShortDateString
Dim enddate As String = wrkdate.ToShortDateString
taSecDesc.Adapter.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM tbDESCRIPT" &
                                                           " LEFT OUTER JOIN tbSECTIONS ON tbDESCRIPT.SECT_ID = tbSECTIONS.ID" &
                                                           " LEFT OUTER JOIN tbEXPENSES ON tbDESCRIPT.ID = tbEXPENSES.DESCRIPT_ID AND tbEXPENSES.REC_DATE Between '" & begdate & "' and '" & enddate & " 23:59:59'" &
                                                           " LEFT OUTER JOIN PersonalExpenses ON tbEXPENSES.REC_DATE = PersonalExpenses.RECEIPT_DATE" &
                                                           " WHERE REC_DATE Is Not NULL ORDER BY tbSECTIONS.ID, tbDESCRIPT.ID, LoadNumber", taSecDesc.Connection)