I have multiple datatables containing multiple columns. I need to combine all of them to a single table. Here is an example:

TABLE1
==================================================
-ID---NAME---AGE---ACOLUMN3---ACOLUMN4---ACOLUMN5-
-----------------------------------------------
-1---JON----23------TRUE------FALSE-------TRUE
-2---JANE----29------TRUE-----FALSE-------FALSE-
-3---JOE----32------TRUE------TRUE--------TRUE
==================================================

TABLE2
==================================================
-ID---NAME---AGE---BCOLUMN3---BCOLUMN4---BCOLUMN5-
-----------------------------------------------
-1---JON----23-------TRUE------FALSE------TRUE
-4---JOEY----34------FALSE-----TRUE-------TRUE
-3---JOE----32-------TRUE------TRUE-------FALSE
==================================================

COMBINED TABLE
=================================================================================
-ID---NAME---AGE---ACOLUMN3---ACOLUMN4---ACOLUMN5---BCOLUMN3---BCOLUMN4---BCOLUMN5
---------------------------------------------------------------------------------
-1---JON-----23------TRUE------FALSE------TRUE-------TRUE------FALSE-------TRUE
-2---JANE----29------TRUE------FALSE------FALSE------FALSE-----FALSE-------FALSE
-4---JOEY----34------FALSE-----FALSE------FALSE------FALSE-----TRUE--------TRUE
-3---JOE-----32------TRUE------TRUE-------TRUE-------TRUE------TRUE-------FALSE
================================================================================


Can someone help me find the most efficient way to combine them. Thanks

In SQL

select Table1.ID,Table1.NAME,Table1.AGE,Table1.ACOLUMN3,Table1.ACOLUMN4,Table1.ACOLUMN5,Table2.BCOLUMN3,Table2.BCOLUMN4,Table2.BCOLUMN5 from Table1 join Table2 on Table1.ID=Table2.ID

:( its coded (multiple dataTable variables) on vb.net its not in SQL or MySQL

Any database that supports SQL queries should work with k's query above. It is a simple inner join with no implementation specific keywords. A very good explanation of joins (inner and outer) with clear examples can be found here

I'm sorry i need further explanation.... These tables are from several datagridviews... This information was not from a database but from user input(I managed to output this information to DataTable variables) . Are you saying I can get these DataTable variables and somehow execute a mysql query?

hello !
i tried to make a query which generate the same result as you want , but prob it that the fields Acolumn3 and others have boolean data type which can not be sum , so the result of the query is something like this
COMBINED TABLE
=================================================================================
-ID---NAME---AGE---ACOLUMN3---ACOLUMN4---ACOLUMN5---BCOLUMN3---BCOLUMN4---BCOLUMN5
---------------------------------------------------------------------------------
-1---JON-----23------TRUE------FALSE------TRUE---------------------------------
-1---JON-----23--------------------------------------TRUE------FALSE-------TRUE
-2---JANE----29------TRUE------FALSE------FALSE---------------------------------
-2---JANE----29--------------------------------------FALSE-----FALSE-------FALSE
-4---JOEY----34------FALSE-----FALSE------FALSE--------------------------------
-4---JOEY----34--------------------------------------FALSE-----TRUE--------TRUE
-3---JOE-----32------TRUE------TRUE-------TRUE-----------------------------------
-3---JOE-----32--------------------------------------TRUE------TRUE-------FALSE

here is a query

select id,name ,age ,Acolumn3 ,Acolumn4 ,Acolumn5,'' as Bcolumn3 ,'' as Bcolumn4 ,''as  Bcolumn5
from table_1
group by id ,name ,age ,Acolumn3 ,Acolumn4 ,Acolumn5
union all
select id ,name ,age ,''  ,'' ,'' ,Bcolumn3 ,Bcolumn4 ,Bcolumn5 
from table_2
group by id,name , age ,Bcolumn3 ,Bcolumn4 ,Bcolumn5
order by id

i know this is not what you want , but this is what i can do for you :)

Regards

What i'm not understanding is why the use of SQL or MySQL if the data isn't coming from there?

If you are working with non database items, you can try parsing each item in the gridview.

Like this:

dt2.rows('index') = dt1.rows('index')


'So, like this

'dt2.row(0) = 0
'dt1.row(1) = 15

dt2.rows(0) = dt.rows(1)

dt2.rows(0) = 15

Ok, I took an hour only for your, to do the code insread of you.
It works like you wanted, so like charm :)

Take a look, and let me know:

Public Sub New()
	Dim table1 As New DataTable("t1")
	Dim table2 As New DataTable("t2")
	table1.Columns.AddRange(New DataColumn() {New DataColumn("ID", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Age", GetType(Integer)), New DataColumn("ACOLUMN3", GetType(Boolean)), New DataColumn("ACOLUMN4", GetType(Boolean)), New DataColumn("ACOLUMN5", GetType(Boolean))})
	table2.Columns.AddRange(New DataColumn() {New DataColumn("ID", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Age", GetType(Integer)), New DataColumn("BCOLUMN3", GetType(Boolean)), New DataColumn("BCOLUMN4", GetType(Boolean)), New DataColumn("BCOLUMN5", GetType(Boolean))})

	table1.Rows.Add(1, "Jon", 23, True, False, False)
	table1.Rows.Add(2, "Jane", 17, False, True, True)
	table1.Rows.Add(3, "Joe", 29, True, True, True)
	table2.Rows.Add(1, "Jon", 23, False, False, True)
	table2.Rows.Add(4, "Joey", 17, True, False, True)
	table2.Rows.Add(3, "Joe", 29, False, False, False)

	'specify which columns are in both tables:
	Dim myColumns As String() = {"ID", "Name", "Age"}
	Dim myColumns2 As String() = {"ID", "Name", "Age", "BCOLUMN3", "BCOLUMN4", "BCOLUMN5"}

	Dim combined As New DataTable()
	'get column names from table1 ansd and name it:
	combined = table1.Clone()
	combined.TableName = "t1_t2"


	'add other columns from table2:
	For Each column As DataColumn In table2.Columns
		If Not myColumns.Contains(column.ColumnName) Then
			combined.Columns.Add(column.ColumnName, GetType(String))
		End If
	Next

	'set to false all cells in advacne:

	'add data from table1 to combined:
	For i As Integer = 0 To table1.Rows.Count - 1
		combined.Rows.Add()
		'set cells to false:
		SetCellsToFalse(i, 9, combined)
		For j As Integer = 0 To table1.Rows(i).ItemArray.Length - 1
			combined.Rows(i)(j) = table1.Rows(i)(j)
		Next
	Next

	'add data from table2 to combined:
	For i As Integer = 0 To table2.Rows.Count - 1
		Dim currentID As Integer = Integer.Parse(table2.Rows(i)(0).ToString())
		Dim query = table1.AsEnumerable().Where(Function(w) CInt(w("ID")) = currentID)
		If Not query.Any() Then
			combined.Rows.Add()
			'set cells to false:
			SetCellsToFalse(i, 9, combined)
			For l As Integer = 0 To combined.Columns.Count - 1
				If l < 3 Then
					combined.Rows(combined.Rows.Count - 1)(l) = table2.Rows(i)(l)
				ElseIf l >= 3 AndAlso l < 6 Then
					combined.Rows(combined.Rows.Count - 1)(l) = False
				Else
					combined.Rows(combined.Rows.Count - 1)(l) = table2.Rows(i)(l - 3)
				End If
			Next
		Else
			For j As Integer = 0 To table2.Rows(i).ItemArray.Length - 1
				If Not myColumns.Contains(table2.Columns(j).ColumnName) Then
					combined.Rows(i)((table1.Columns.Count - myColumns.Length) + j) = table2.Rows(i)(j)
				End If
			Next
		End If
	Next
End Sub

Private Sub SetCellsToFalse(row As Integer, columns As Integer, ByRef table As DataTable)
	For i As Integer = 3 To columns - 1
		table.Rows(row)(i) = False
	Next
End Sub

Thanks for your time on helping me Mitja. I know you went out your way to help me on this. The reason i was asking for help is because i didn't know if it was possible to join both tables with a simple built-in code. What i'm worried is how efficient and how long will it take to combine 5 tables( with 7 Acolumns each) like the ones I showed into just 1 table. I just posted a quick and easy table as an example, just to show if it was possible without doing loops and conditional statements. Thanks I really appreciate it.. I'll use your code as a baseline and tweak it to be able use all 5 tables.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.