How to add a column from one table to another in SQL?-CodePudding

I have two tables:

Table1 has these columns: location_id, street_address, city, state_province

Table2 has this column: country_name

What I want is to display, is this: location_id, street_address, city, state_province, country_name

Basically, I want to get the country_name from Table2 and add it/append it to Table1. I thought of using the join command but I can't seem to get it to work. Any help will be appreciated.

CodePudding user response：

The ideal way would be to have a common value to join the tables:

Table 1 would have to have a field to identify which country it belongs to, like so: location_id, street_address, city, state_province, country_id

Table 2 would need a primary key to identify the country code. So Table 2 would be like: country_id, country_name

Then you could join on country_id to display the tables like you want.

The query would be something along the lines of:

SELECT t1.*, t2.country_name from table1 as t1
LEFT JOIN table2 as t2 using(country_id);

CodePudding user response：

I agree with NBK above. If you share Table_2's fields and your current join statement it could help get a more specific answer.