Inner Join with 2 or more tables

If you are getting data from more than 2 tables you need to surround the inner joins in brackets. For this example I am using the just released SQL Compact Edition. You will find a sample NorthWind database in the sdk. Note to work with a SQL CE database you need to add a reference to the System.Data.Sqlce.dll you find in the directory you installed sql server ce.

Imports System.Data.SqlServerCe
Imports System.Text
 
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim conn As New SqlCeConnection("data source='NorthWind.sdf'; mode=Exclusive;")
        Dim sbCommand As New StringBuilder
        Dim dt As New DataTable
        sbCommand.Append("Select Orders.[Customer ID], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price] From ")
        sbCommand.Append("(Orders Inner Join [Order Details] on Orders.[Order ID] = [Order Details].[Order ID]) ")
        sbCommand.Append("Inner Join Products on [Order Details].[Product ID]= Products.[Product ID]")

        Dim da As New SqlCeDataAdapter(sbCommand.ToString, conn)
        da.Fill(dt)
        DataGridView1.DataSource = dt
        With DataGridView1.Columns("Unit Price").DefaultCellStyle
            .Format = "c"
            .Alignment = DataGridViewContentAlignment.MiddleRight
        End With
    End Sub
End Class