Question How to join tables?

[hdave]

Hello everyone!
I've been using Java for a number of years, and now I'm at the beginning again with VB.NET.

How would I go about joining two tables?
- order doesn't matter, as long as i can print/display the order


Here's an example of what I'd like to do:

table 1

data 1 , data 2 , date time
12 , 23 , 2010/8/20 10:00:00
12 , 25 , 2010/8/20 10:01:00
13 , 24 , 2010/8/20 10:02:00


table 2

date 3 , data 4 , date time
2.2 , 12.5 , 2010/8/20 10:00:00
2.4 , 12.5 , 2010/8/20 10:01:00
2.3 , 13.1 , 2010/8/20 10:03:00




result table

data 1 , data 2 , data 3 , data 4 , date time
12 , 23 , 2.2 , 12.5 , 2010/8/20 10:00:00
12 , 25 , 2.4 , 12.5 , 2010/8/20 10:01:00
13 , 24 , __ , __ , 2010/8/20 10:02:00
__ , __ , 2.3 , 13.1 , 2010/8/20 10:03:00



Any input would be greatly appreciated.

Thanks!

 

[kulrom]

Try this (i haven't tested it yet ... writing off the top of my head)

            SELECT 
		T1.data1, T1.data2, T1.date, T1.time, 
		T2.data3, T2.data4, T2.date, T2.time
	FROM 
		TTable1 AS T1
	FULL JOIN 
		TTable2 AS T2
	ON
		T1.time = T2.time

 

[hdave]

How would I use that SQL query on my VB.NET table?

 

[kulrom]

how about using it together with SqlCommand object e.g.

Dim connection As New SqlConnection(connectionstringhere)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText =  "SELECT T1.data1, T1.data2, T1.date, T1.time, T2.data3, T2.data4, T2.date, T2.time " & _
	                          "FROM TTable1 AS T1 FULL JOIN  TTable2 AS T2 ON T1.time = T2.time"

connection.Open()
Dim table as New datatable
table.Load(command.ExecuteReader)
connection.Close()

then you can bind some controls to this data e.g.

MyGrid.DataSource = table

P.S. as mentioned i haven't tested the sql query yet but, if i understand your question it should work for you

 

[MattP]

Converting SQL to LINQ, Part 9: Full Outer Join (Bill Horst) - The Visual Basic Team - Site Home - MSDN Blogs