How to write query just join tables horizontally ?

[mrchonginhk]

I know it doesnt make sense but this is the situation I am facing.

I have 3 tables,
1st table has column A, B
2nd table has column C, D
3rd table has column E,F
I need to make a new query so the output is now a single table having 6 columns
A,B,C,D,E,F

How to do this ?
There is no index in all 3 tables.
The 1st row of these 3 tables need to goto the 1st line of the target query table, etc etc

Thanks

 

[alansidman]

SELECT Table1.A, Table1.B, "" AS C, "" AS D, "" AS E, '' AS F
FROM Table1
Union
SELECT "" AS a, "" AS b, Table2.c, Table2.d, "" AS e, "" AS f
FROM Table2
Union
SELECT "" AS a, "" AS b, '' AS c, "" AS d, Table3.e, Table3.f
FROM Table3;

 

[Micron]

There is no index in all 3 tables.

I know what an index is, but your statement is inconclusive at best. Are you saying there is no commonality between any 2 fields between any of the tables? Whether you have any indexes or not is irrelevant to having common fields.

If you really have no related fields between tables and only need one value from one field from the first record of each table, then the only thing I can think of is a sub query for every field after the first. In other words

SELECT First(tbl1.A) AS FirstOfA, (SELECT First(tbl2.B) AS FirstOfB FROM tbl2), (SELECT First(tbl3.C) AS FirstOfC FROM tbl3), 
(SELECT First(tbl4.D) AS FirstOfD FROM tbl4)
FROM tbl1;

This would be the pattern, but I'm going to stop at 4 tables and fields as it's getting tedious. Besides, it's all speculation based on what might be my flawed understanding of your situation. If you wish to try it on 4 to start, add the rest if it works. However, it must end in "FROM tbl1;" or whatever your first table name is.

Of course, you have to provide your own table and field names.