CODEWITHSUNDEEP
sqlsqlite
Steven Smethurst
Steven Smethurst

Reputation: 4614

SQL inner join two tables with the same column names

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.

Upvotes: 14

Views: 26370

Answers (2)

MPelletier
MPelletier

Reputation: 16657

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.

Upvotes: 9

AllenG
AllenG

Reputation: 8190

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...

Upvotes: 13

Related Questions