CODEWITHSUNDEEP
mysqlsqljoin
stamaimer
stamaimer

Reputation: 6475

can i rename the table which generated by inner join?

I have two tables, the structure of them as follows:

table1:

+----+-----------------------+----------------------+
| id | rank_before_promotion | rank_after_promotion |
+----+-----------------------+----------------------+
| 01 |         GR01          |         GR02         |
| 02 |         GR03          |         GR04         |
| 03 |         GR03          |         GR05         |
| 04 |         GR06          |         GR07         |
+---------------------------------------------------+

table2:

+------+------+
| rank | area | 
+------+------+
| GR01 | 1000 |    
| GR02 | 1200 |     
| GR03 | 1400 |     
| GR04 | 1600 | 
+-------------+

I want to calculate the difference between the area of rank_before_promotion and the area of rank_after_promotion with this sql:

     SELECT id, rank_before_promoton, rank_after_promotion, tmp1.area-tmp2.area 
     FROM (table1 INNER JOIN table2 ON rank_before_promotion = rank) AS tmp1,
          (table1 INNER JOIN table2 ON rank_after_promotion = rank) AS tmp2;

There are some error when i run this sql. Can i rename the table which generated by join? And is there a method to print the table generated by join?

The desired output will be:

+----+-----------------------+----------------------+--------+
| id | rank_before_promotion | rank_after_promotion | differ |
+----+-----------------------+----------------------+--------+
| 01 |         GR01          |         GR02         |   200  |
| 02 |         GR03          |         GR04         |   200  |
| 03 |         GR03          |         GR05         |   400  |
| 04 |         GR06          |         GR07         |   200  |
+---------------------------------------------------+--------+

Upvotes: 1

Views: 1468

Answers (1)

Hart CO
Hart CO

Reputation: 34774

I don't fully understand what you're attempting to achieve subtracting a string from another, but here's a version of your query that executes:

 SELECT id, rank_before_promotion, rank_after_promotion, b.area-c.area 
 FROM table1 a
 INNER JOIN table2 b
    ON a.rank_before_promotion = b.rank
 INNER JOIN table2 c
    ON a.rank_after_promotion = c.rank;

Demo: SQL Fiddle

Upvotes: 1

Related Questions