vb.net 中的内连接 SQL 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16027369/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inner join SQL syntax in vb.net
提问by ivandinglasan
This is my select SQL syntax to display the category choices, but what I need to do is display the corresponding category chosen based on equipment_id
这是我用来显示类别选择的select SQL语法，但我需要做的是显示根据设备ID选择的相应类别
I got 2 tables, tblOfficeEquipmentCategory and tblOfficeEquipmentProfile
我有 2 个表，tblOfficeEquipmentCategory 和 tblOfficeEquipmentProfile
I need to inner join tblOfficeEquipmentProfile so I can add the WHERE equipment_id = '"txtid.text"'
我需要内部加入 tblOfficeEquipmentProfile 以便我可以添加 WHERE equipment_id = '"txtid.text"'
What would be the corresponding SQL syntax
相应的 SQL 语法是什么
Public Sub DisplayCategory()
'based on oe_id
Dim sqlconn As New SqlClient.SqlConnection
sqlconn.ConnectionString = "server = SKPI-APPS1;" & _
"Database = EOEMS;integrated security=true"
Dim sqlcommand As SqlCommand
sqlconn.Open()
Dim da As New SqlDataAdapter("select * from tblOfficeEquipmentCategory", sqlconn)
Dim dt As New DataTable
da.Fill(dt)
cmbCategory.DataSource = dt
cmbCategory.ValueMember = "CAT_Name"
cmbCategory.DisplayMember = "CAT_ID"
sqlconn.Close()
End Sub
回答by John Woo
Assuming equipment_idis located on table tblOfficeEquipmentProfileand a column CAT_IDwhich links it to table tblOfficeEquipmentCategory.
假设equipment_id位于 tabletblOfficeEquipmentProfile和将CAT_ID它链接到 table的列上tblOfficeEquipmentCategory。
SELECT a.CAT_Name
FROM tblOfficeEquipmentCategory a
INNER JOIN tblOfficeEquipmentProfile b
ON a.CAT_ID = b.CAT_ID
WHERE b.equipment_id = @ID
To further gain more knowledge about joins, kindly visit the link below:
要进一步了解有关联接的更多信息，请访问以下链接：
回答by Joel Coehoorn
Best to start thinking about how to separate your data access from your presentation code. You need something like this:
最好开始考虑如何将数据访问与演示代码分开。你需要这样的东西：
Public Function DisplayCategory()
cmbCategory.ValueMember = "CAT_Name"
cmbCategory.DisplayMember = "CAT_ID"
cmbCategory.DataSource = GetEquipmentCategories(Convert.ToInt32(txtid.text))
End Sub
Public Function GetEquipmentCategories(ByVal EquipmentID As Integer) As DataTable
'based on oe_id
Dim sql As String = "SELECT a.CAT_ID, a.CAT_Name" & _
" FROM tblOfficeEquipmentCategory a" & _
" INNER JOIN tblOfficeEquipmentProfile b ON a.CAT_ID = b.CAT_ID" & _
" WHERE b.equipment_id = @ID"
Dim result As New DataTable
Using cn New SqlConnection("server=SKPI-APPS1;Database=EOEMS;integrated security=true"), _
cmd As new SqlCommand(sql, cn)
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = EquipmentID
cn.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader
result.Load(rdr)
End Using
End Using
Return result
End Function
