SQL inner join two tables with the same column names

14
votes

I have two tables with a variable amount of columns. (I don't know how many columns or what there names will be) for example Table A and Table B.

TableA: 

ID | B_ID | {variable}

TableB

ID | {variable}

Query: 

SELECT TableA.*, TableB.* FROM TableA INNER JOIN TableB ON TableA.B_ID= TableB.id;

When TableA and TableB both have a column with a same name I can't distinguish between the two different columns. For example of both tables has the column "Name" this query would result in :

ID | ID | B_ID | NAME | NAME |
 1 | 35 | 35   | bob  | jim  |

What I am looking for is a way to differentiate between the two tables. Preferably with a prefex for the column names such as. 

TableA_ID | TableB_ID | TableA_B_ID | TableA_NAME | TableB_NAME |
        1 |        35 |          35 |         bob |         jim |

I know of the "AS" keyword but the problem is that I don't know what the column names are going to be before hand. (I don't know if TableA or TableB are going to have the column Name)

So my question is

How do you differentiate the columns between the two tables with a INNER JOIN when the tables may have the same column names ?

I am using SQLite3.

2
sqlsqlite

2 Answers

13
votes

Your result set (given your query) should have all of the TableA columns followed by all the TableB colums, so when you get to the second ID colum, you know you're into the TableB data.

That said, it is would seem odd to me that you're querying all the data out of two tables about which you know functionally nothing...

8
votes

This is admittedly a hack solution, but this:

SELECT TableA.*, "#", TableB.* 
FROM TableA INNER JOIN TableB ON TableA.B_ID= TableB.id;

Would produce a list of results which would be divided in two blocks, left and right of the # column.