Mysql join query on multiple tables

Question

I have three tables and in those tables these basic fields

contacts

con_id (primary key)
con_name
con_work_id
con_country_id

work

work_id (primary key)
work_company_name
work_country_id

country

country_id (primary key)
country_name

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

Patrick Kostjens · Accepted Answer · 2013-05-15 17:23:47Z

2

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.

answered May 15, 2013 at 17:23

Patrick Kostjens

5,1006 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql join query on multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related