how to join 2 tables have different key name but same key value-CodePudding

I have 2 tables ,table1 and table2.

table1:

id case_id   name
1  177       John
2  264       Tom
3  378       Jin

table2:

id td_case_id   total
1  177          33
2  264          56
3  986          98

I want to join the 2 tables by both case_id from table1 and td_case_id from table2.

The output should be something like:

id case_id/ta_case_id   name   total
1  177                  John   33
2  264                  Tom    56

CodePudding user response：

You can use the SQL JOIN clause to join two tables together based on a common column. To join the table1 and table2 tables on the case_id and td_case_id columns, respectively, you can use the following SQL query:

SELECT table1.id, table1.case_id, table1.name, table2.total
FROM table1
JOIN table2
ON table1.case_id = table2.td_case_id;

This query will return a result set with the columns id, case_id, name, and total from both tables, where the case_id column from table1 is matched with the td_case_id column from table2.

Alternatively you could use alias to refer the table in question

SELECT t1.id, t1.case_id, t1.name, t2.total
FROM table1 t1
JOIN table2 t2
ON t1.case_id = t2.td_case_id;

You could also specify the column names from both tables in the SELECT statement

SELECT table1.id as id, table1.case_id as case_id, table1.name as name, table2.total as total
FROM table1
JOIN table2
ON table1.case_id = table2.td_case_id;