I am new to vb and sql . please bear with me
I have a database name database 1. I have column name "column A" and “column B" in all tables. I want to retrieve tables name and sum of (column A – column B) of all tables like below
table1 ( columnA - columnB)
table2 ( columnA - columnB)
table3 ( columnA - columnB)
if i have some manageable table I can use table names in sql statement
SELECT "Table1" AS Table, SUM(a) - SUM(b) AS Result FROM table1
SELECT "Table2", SUM(a) - SUM(b) FROM table2
SELECT "Table3", SUM(a) - SUM(b) FROM table3
I would like to know is it possible to create a cursor to dynamically collect the data from all tables ?
I have tried the following code, When I run the sql statement I get the following error
Syntax error (missing operator) in query expression 'GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ' - ')'.
Dim CmdStr As String = "SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ' - ')FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('a','b')AND TABLE_SCHEMA='database1'GROUP BY COLUMN_NAME;"
or is there any way to do it in vb.net and ms access database? any bit of info would be helpful