hisystems/DatabaseObjects — Issue #6: Improve creating multiple table joins
Author: hisystems
Created: 2012-03-04 06:05:08+00:00
State: open

Currently, the multiple table joins are supported, however it is not very intuitive when creating two or more table joins. Creating one table join is somewhat intuitive:

```vb.net
Dim table1 As New SQLSelectTable("Table1")
Dim table2 As New SQLSelectTable("Table2")

Dim selectStatement As New SQLSelect()
With selectStatement.Tables.Joins.Add(table1, SQLSelectTableJoin.Type.Inner, table2)
.Where.Add("Table1Key", SQL.ComparisonOperator.EqualTo, "Table2Key")
End With

```

However, creating multiple joins is not, because the second join is really based on the results of the first join, and not really from table2 to table3 as the code reads. The join is from table1+table2 to table3:

```vb.net
Dim table1 As New SQLSelectTable("Table1")
Dim table2 As New SQLSelectTable("Table2")
Dim table3 As New SQLSelectTable("Table3")

Dim selectStatement As New SQLSelect()
selectStatement.Tables.Add(table1)

With selectStatement.Tables.Joins.Add(table1, SQLSelectTableJoin.Type.Inner, table2)
.Where.Add("Table1Key", SQL.ComparisonOperator.EqualTo, "Table2Key")
End With

With selectStatement.Tables.Joins.Add(table2, SQLSelectTableJoin.Type.Inner, table3)
.Where.Add("Table2Key", SQL.ComparisonOperator.EqualTo, "Table3Key")
End With
```

```SQL
SELECT * FROM (([Table1] INNER JOIN [Table2] ON [Table1].[Table1Key] = [Table2].[Table2Key]) INNER JOIN [Table3] ON [Table2].[Table2Key] = [Table3].[Table3Key])
```