CODEWITHSUNDEEP
sqlsqlite
avocado
avocado

Reputation: 31

Join two tables juxtaposing columns with same name sql

I have two sqlite3 tables with same column names and I want to compare them. To do that, I need to join the tables and juxtapose the columns with same name. The tables share an identical column which I want to put as the first column.

Let's imagine I have table t1 and table t2

Table t1:

SharedColumn | Height | Weight
        A    |   2    |   70
        B    |   10   |   100

Table t2:

SharedColumn | Height | Weight
        A    |   5    |   25
        B    |   32   |   30

What I want get as a result of my query is :

SharedColumn | Height_1 | Height_2 | Weight_1 | Weight_2
        A     |    2     |     5    |    70    |    25    
        B     |    10    |     32   |    100   |    30

In my real case i have a lot of columns so I would like to avoid writing each column name twice to specify the order.

Renaming the columns is not my main concern, what interests me the most is the juxtaposition of columns with same name.

Upvotes: 1

Views: 2465

Answers (2)

Fatema Tuz Zuhora
Fatema Tuz Zuhora

Reputation: 3706

Try the following query to get the desired result!!

SELECT t1.Height AS Height_1, t1.Weight AS Weight_1, t1.sharedColumn AS SharedColumn
   t2.Height AS Height_2, t2.Weight AS Weight_2
   FROM t1 INNER JOIN t2
   ON t1.sharedColumn = t2.sharedColumn
   ORDER By t1.sharedColumn ASC

After that, you can fetch the result by following lines:

$result['SharedColumn'];
$result['Height_1'];
$result['Height_2'];
$result['Weight_1'];
$result['Weight_1'];

Upvotes: 1

sagi
sagi

Reputation: 40481

There is no way to do that directly in SQL especially because you also want to rename the columns to identify their source, you'll have to use dynamic SQL and honestly? Don't! .

Simply write the columns names, most SQL tools provide a way to generate the select, just copy them and place them in the correct places :

SELECT t1.sharedColumn,t1.height as height_1,t2.height as height_2 ...
FROM t1
JOIN t2 ON(t1.sharedColumn = t2.sharedColumn)+

Upvotes: 3

Related Questions