Sql - 加入4个表 [英] Sql - joining 4 tables
问题描述
我为我的开场白而道歉，但必须说......我即将问一个对有经验的用户来说似乎很简单的问题。请不要贬低我，说一些对我来说应该是显而易见的事，否则你不会为我写代码，因为这不是我要求的。当我提出一个问题时，我已经厌倦了像被一个小势子一样对待。一个简单有用的手和方向指向是我所要求的，而不是代码重写，所以我可能理解未来，并且不会被要求在这个问题上再次得到帮助。
我正在尝试加入另一个表来写入数据。 JOIN我目前工作正常，但当我添加另一个JOIN语句时，我迷路了。我的技能组合在多个连接方面是有限的，这种加入非常复杂。
我有2个表，tbSECTIONS和tbDESCRIPT，它们包含公共数据，这是只是一个段名表，分配了一个数字，同样，描述的tbDESCRIPT表分配了一个数字，并由tbEXPENSES表连接和引用。这很好。
tbEXPENSES是一个包含费用信息的表格，它使用上面的表格根据分配给输入数据的部分和描述编号对项目进行分类，将它写入Weekly_Expense_Table。这很好。
我现在需要将未写入的内容与Weekly_Expense_Table分开，并将该信息放入PersonalExpenses表中。我试图通过多个地方和多种方式加入PersonalExpenses表，但我所做的一切都是正确的。我缺乏知识取决于我应该加入哪个表格？唯一可能的共同点是在PersonalTable中匹配列名的tbDESCRIPT表。我确信我没有很好地解释这一点，因为我缺乏理解，但会添加任何可能有助于破译我的问题的代码。只需简单地询问代码需要哪些部分。
我的问题区域是：
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"
当我尝试运行查询时，显然没有正确加入表，因为我收到Key not found错误。
我尝试过：
我的SQL JOIN目前编码如下：
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)
推荐答案
总是加入应该是主键和forgein键概念
sql存储日期时间字段中的日期和时间。所以你的加入表
下列字段没有共同的日期时间值
tbEXPENSES.REC_DATE = PersonalExpenses.RECEIPT_DATE
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
这篇关于Sql - 加入4个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
