Need big help on this one

[ninjatalon]

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

[kingsonprisonic]

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

[ninjatalon]

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

[Reverend Jim]

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

[ninjatalon]

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?

[M.Waqas Aslam]

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

[ninjatalon]

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

[Begginnerdev]

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

[Mitja Bonca]

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

[ninjatalon]

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.