Click here to Skip to main content
15,063,784 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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)
Posted
Updated 12-Nov-17 22:53pm
Comments
PIEBALDconsult 12-Nov-17 13:07pm
   
Two things right off -- do not use strings for dates and always use a parameterized statement.

Other than that, what database system?
A_Griffin 12-Nov-17 13:14pm
   
I am not an expert in SQLServer, but I thought your AND condition should be part of the WHERE conduition and come after the JOINs. eg SELECT ... FROM ... JOIN [JOIN...} WHERE ... AND ... ORDER BY
Also, can I suggest using the AS keyword to create simplified monikers for your tables - it makes the SQL more compact and, IMO, easier to read.
SELECT * FROM tbDESCRIPT AS D
LEFT OUTER JOIN tbSECTIONS AS S ON D.SECT_ID = S.ID ... ect
CHill60 13-Nov-17 9:42am
   
You can use AND as part of the ON clause for the join. I agree with you on the use of aliases for the tables but using AS is largely a matter of personal preference
K3JAE 12-Nov-17 13:23pm
   
OK, Thank you for that. I can adapt the SQL statement using parameterized statements, after I get this working properly, then will convert that over as I also agree it makes it a bit neater and compact.

I am not sure how to pass the date then without using a string?

I am using MSSQL as my DB.
an0ther1 12-Nov-17 20:27pm
   
Joining tables on dates is a bad idea. There should be a key between TbExpenses & PersonalExpenses that joins the data, otherwise you will get all Personal Expenses that match the date value & hence you may get duplicates - this depends on how your date values are stored - if you are using a DateTime datatype & populating the time component then less likely, if you are just populating the date value then you would get all Personal Expense records.
Additionally, as A_Griffin has said, your join statement for the tbExpenses table duplicates your where statement - there is no sense doing this.

Kind Regards
Jörgen Andersson 13-Nov-17 4:42am
   
Could you update the question with the table definitions?

1 solution

Always Joining should be with primary key and forgein key concept

sql store date and time in datetime field .so your in your joining tables

have no common datetime values for the bellow fields

tbEXPENSES.REC_DATE = PersonalExpenses.RECEIPT_DATE
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900