Combining multiple Access tables using C#

[Yaridovich]

Hello everyone,

I'm having a bit of trouble with something. What I'm trying to do is create a new Access table by combining other already existing Tables.
For instance, let's consider Table1 (with columns A, B and C), Table2 (with columns A, D and E) and Table3 (with columns A, F and G), where Table1.A = Table2.A = Table3.A. From these, I want to create a new table TableResult, with columns A, B, C, D, E, F and G, with all the data contained in them. There's one catch: I want the code to get the names of the other columns, apart from column A.
I've thought about using Union, but I need to actually create a new table by combining the previous tables.

Any ideas?

Thanks in advance!

[Rabbit]

Use a join. But you should only do this if the tables are related 1 to 1. Otherwise you're going to end up duplicating information and end up with denormalized data.

[Yaridovich]

@Rabbit - Can you give me an example as how to do it using Join? Also, I forgot to add something to the original post. I just edited it and added this: "There's one catch: I want the code to get the names of the other columns, apart from column A."

[Rabbit]

The SQL code to join a table is

SELECT *
FROM tableA
INNER JOIN tableB
ON tableA.IDField = tableB.IDField

[Yaridovich]

@Rabbit - But how can I save the resulting table into a new table? Is there any way of combining "Create Table" and "Join"?

[Rabbit]

You can use a select into to create a table from a query.

If you're strugling with basic SQL, you should read a tutorial before taking on a project.

[Yaridovich]

It's just that, in the work I do, I don't use SQL queries much, it's basically just simple stuff. Now I'm needing to do some more complex stuff. And it seems that creating a table from an inner join isn't that simple.

I'm trying to do this, but it's not working. I've tried moving the closing parenthesis to the end, but it doesn't work either.

SELECT *
INTO TableResult
FROM
(
   SELECT *
   FROM Table1
   INNER JOIN Table2
   ON Table1.A = Table2.A
)
INNER JOIN Table3
ON Table1.A = Table3.A

[Rabbit]

There's no need to subquery the first join.

But the reason it's not working is because you didn't alias the subquery. Outside of a subquery, you don't have access to the table names used inside the subquery.

[Yaridovich]

Could you help me write the correct query? I've tried multiple things, but none are working, there's always some syntax error.

I've tried these:

SELECT *
INTO TableResult
FROM
(
SELECT *
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.A = T2.A
INNER JOIN Table3 AS T3
ON T1.A = T3.A
)

SELECT *
INTO TableResult
FROM
(
SELECT *
FROM
(
SELECT *
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.A = T2.A
)
INNER JOIN Table3 AS T3
ON T1.A = T3.A
)

[Rabbit]

Like I said, a subquery isn't necessary.

SELECT ...
INTO ...
FROM tableA
INNER JOIN tableB
ON tableA.ID = tableB.ID
INNER JOIN tableC
ON tableA.ID = tableC.ID

[Yaridovich]

I tried that, but I just remembered I tried that while using alias. I'm gonna try it again, this time I'll do it like that. I'll then get back here to tell you if it worked. Thanks!

[Yaridovich]

That didn't work, but it helped me find the correct syntax - I just need to add parenthesis to the first join. Like this:

SELECT *
INTO TableResult
FROM (Table1
INNER JOIN Table2
ON Table1.A = Table2.A)
INNER JOIN Table3
ON Table1.A = Table3.A

Thanks for the help!