Selecting from the same table multiple times

[fabricator_guy]

I have two Oracle tables.
Table1
Toolid, Description, Description2
1000, drill, colbalt
1001, drill, carbide
Table2
Toolid, pos, value
1000, 1 , .250
1001 , 1 , .500
1000, 2, 2.0
1001, 2 , 3.0
1000, 3 , 4.0
1001, 3 , 5.0
The result I am looking for is to fill a DataGridView with
Toolid, Description, Description2, Dia, Flute_length, OAL
1000, drill , colbalt , .250, 2.0 ,4.0
1001, drill , carbide , .500, 3.0 ,5.0
This is what I have so far code wise, I can add one of the items from table 2, but can’t get any more than one not matter what I try.
Imports System

Imports System.Data

Imports System.Data.OleDb



Public Class Form1

Private bindingSource1 As New BindingSource()

Private dataAdapter1 As New OleDbDataAdapter()

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Me.DataGridView1.DataSource = Me.bindingSource1
Dim connStr As String

Dim Sql As String

connStr = "Provider=MSDAORA;Data Source=mydata.com;Persist Security Info=True;Password=tools;User ID=me"


Sql = "SELECT table1.toolid, table1.description, table1.description2, table2.value AS Diameter FROM table1, table2 WHERE table1.toolid = table2.toolid AND table2.pos = 1"




Me.dataAdapter1 = New OleDb.OleDbDataAdapter(Sql, connStr)




Dim commandBuilder1 As New OleDbCommandBuilder(Me.dataAdapter1)

Dim table As New DataTable()

table.Locale = System.Globalization.CultureInfo.InvariantCulture

Me.dataAdapter1.Fill(table)

bindingSource1.DataSource = table


' Resize the DataGridView columns to fit the newly loaded content.

DataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)


End Sub

End Class
Any suggestions would be greatly appreciated

 

[cjard]

You have to pivot table 2 before you join it:

SELECT * FROM
  t1
  INNER JOIN
  (
    SELECT 
      toolid, 
      MAX(case when pos = 1 then value end) as dia,
      MAX(case when pos = 2 then value end) as flute,
      MAX(case when pos = 3 then value end) as len
    FROM
      t2
    GROUP BY
      toolid
  ) t2
  USING(toolid)

note, the columns from this query will not be updateable unless you write a custom set of queries to update them