How can I join multiple tables?-CodePudding

I have a database with a table that looks like this. I need a query that will output both the address name of the worker and the work location.

Tables

The output should look like this.

Here is the query I'm trying to use.

SELECT workers.first_name, workers.last_name, workers.address_code, lib_address.address_name AS personal_address, work_details.address_code, work_details.work_type, lib_address.address_name AS work_address
FROM workers
JOIN work_details ON workers.work_code=work_details.work_code
JOIN lib_address ON work_details.address_code=lib_address.address_code;

CodePudding user response：

You must use 2 copies for address table.

address_code in workers and in work_details are not dependent and needs in independent lib_address copies to be joined.

SELECT *
FROM workers w
JOIN lib_address la_1 ON w.address_code = la_1.address_code
JOIN work_details wd ON w.work_code = wd.work_code 
JOIN lib_address la_2 ON wd.address_code = la_2.address_code

CodePudding user response：

select w1.first_name,
w1.last_name,
w1.address_code,
l1.address_name as Personal_address,
l1.address_code,
w2.work_type,
w2.work_address from workers w1,lib_address l1,work_details w2
where w1.address_code = l1.address_code
and l1.address_code = w1.address_code
order by first_name;