CODEWITHSUNDEEP
mysqljoin
Wonder Works
Wonder Works

Reputation: 73

Mysql join query on multiple tables

I have three tables and in those tables these basic fields

contacts

work

country

I'm trying to run a query which displays the con_name, work_company_name and then the country name for BOTH the contact and the work company.

I've tried this;

SELECT *
FROM contacts
LEFT JOIN work ON contacts.con_work_id = work.work_id
LEFT JOIN country ON contacts.con_country_id = country.country_id
LEFT JOIN country ON work.work_country_id = country.country_id

But of course this doesn't work because the last join causes a clash with the second one. I'm almost there, but can't get the query to display the country_name associated with both the contact AND the work company.

I'd appreciate a way forward.

Many thanks,

Wonder

Upvotes: 1

Views: 61

Answers (1)

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

The following should work:

SELECT *
FROM contacts
LEFT JOIN work ON contacts.con_work_id = work.work_id
LEFT JOIN country c1 ON contacts.con_country_id = c1.country_id
LEFT JOIN country c2 ON work.work_country_id = c2.country_id

The trick is to add an alias to the table, so that it is possible to distinguish the two.

Upvotes: 2

Related Questions