vb2010 oledb select where select result

Question

I have a small issue dealing with oledb. I have two excel tables: one includes systems and responsible persons

    [person1|system1]
    [person1|system2]
    [person1|system3]
    [person2|system4]
    [person2|system5]
    ...

and another information about the systems

    [system1|location|weight|height]
    [system2|location|weight|height]
    ...

Now I want to be able to select a person and write the system information (for the systems of this person) to a datagridview

BY THE WAY: I can not change the tables as I get them from somewhere else.

My code so far:

    Using cn As New System.Data.OleDb.OleDbConnection
        Dim Builder As New OleDbConnectionStringBuilder With _
            { _
                .DataSource = "reports\PM.xlsx", _
                .Provider = "Microsoft.ACE.OLEDB.12.0" _
            }
        Builder.Add("Extended Properties", "Excel 12.0;")
        cn.ConnectionString = Builder.ConnectionString

        cn.Open()

        Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
            cmd.CommandText = "SELECT F2 AS system FROM [table1$] WHERE F1 ='" & ComboBox2.SelectedItem & "'"
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader

            dtSheet1.Load(dr)
            DataGridView1.DataSource = dtSheet1
        End Using
    End Using

Thatworks perfect so far, I recieve all system names in the datagridview but I have no clue how to select from the other table with the results from the first. I need something like

     SELECT F2 AS location, F3 AS weight, F4 AS hight FROM [table2$] WHERE F1 =ONE OF THE RESULTS FROM THE QUERY ABOVE'"

Thanks in advance, I really appreciate your help!!

Answer 1

You can join the data from both tables using your original query as the criteria:

SELECT t2.F2 AS location, t2.F3 AS weight, t2.F4 AS hight 
FROM [table2$] as t2 
  JOIN [table1$] as t1 
    ON t2.f1 = t1.f1 
WHERE t1.F1 ='" & ComboBox2.SelectedItem & "'"