How woud I join this query in one single SQL statement?

Question

Let's say I have these tables, with these fields:

companies: company_id | name | num_employees

companies_countries: company_id | country_id

countries: country_id | country_iso_code

Assuming this is a 1:1 relationship: How can I join the country_iso_code directly into the company recordset, when I fetch all the companies? I think I would need two joins here?

Answer 1

A simple example :

select c.name, n.country_iso_code
from companies c,
companies_countries x,
countries n
where x.company_id = c.company_id
and n.country_id = x.country_id

Edit

For a good intro to JOIN, have a look at SQL JOIN.

Answer 2

Yes, you would need two joins.

You can create a view and fake a single join though...

Answer 3

select companies.* from countries, companies_countries, companies where countries.country_id=companies_countries and companies_countries.company_id=company_id and countries.country_iso_code='xxxx'

where xxxx is the iso code you want to match

Answer 4

SELECT co.*, cu.country_iso_code 
FROM companies co 
LEFT JOIN companies_countries cc ON cc.company_id = co.company_id
LEFT JOIN countries c ON c.country_id = cc.country_id

Why LEFT JOIN and not the WHERE condition like in other examples? A join table (companies_countries) is not typically used in 1:1 relationships - it is overnormalization.

When your relationship ceases to be 1:1:

SELECT co.*, GROUP_CONCAT(cu.country_iso_code)
FROM companies co 
LEFT JOIN companies_countries cc ON cc.company_id = co.company_id
LEFT JOIN countries c ON c.country_id = cc.country_id

This will return results like

CompanyA | Canada,USA,Mexico
CompanyB | Ireland,UK,Japan

Answer 5

SELECT 
    companies.company_id, 
    companies.name, 
    companies.num_employees, 
    countries.country_iso_code
FROM
    companies
LEFT JOIN
    companies_countries ON (companies_countries.company_id = companies.company_id)
LEFT JOIN
    countries ON (countries.country_id = companies_countries.country_id);

A query that uses a LEFT JOIN instead of an INNER JOIN or an implicit join, will return companies even when they have no country assigned. On the other hand, a query with an INNER JOIN would skip companies that do not have an assigned country in companies_countries.

Note that your design is implying that each company can be assigned more than one country. If you want to enforce only one country for each company, simply put a country_id column in your companies table. You would not need the companies_countries table.

Answer 6

select c.company_id, c.name, c.num_employees, co.county_iso_code from
companies c, companies_countries cc, countries co
where c.company_id = cc.company_id 
and cc.country_id = co.country_id

Answer 7

select name, country_iso_code 
from companies left join companies_countries 
   on (companies.company_id = companies_countries.company_id)
left join countries 
   on (companies_countries.country_id = countries.country_id)

Answer 8

This is not a 1:1 relationship. A country can have more the one company.

This is either a 1:N relationship (for some reason implemented using two relational tables), or the M:N relationship which describes multinational companies.

If this is a 1:N relationship, you could just put the country_code field into the companies table, in which case one join would be enough:

SELECT  *
FROM    companies co
LEFT JOIN
        countries cn
ON      cn.country_code = co.country_code

Your design is viable for both 1:N and M:N relationships, in which case two joins are required:

SELECT  co.*, cn.*
FROM    companies co
LEFT JOIN
        company_countries cc
ON      cc.company_id = co.company_id
LEFT JOIN
        countries cn
ON      cn.country_code = cc.country_code

If this is a 1:N relationship, you should make company_id a PRIMARY KEY in the company_country table.

If this is a M:N relationship, you should make a composite PRIMARY KEY on company_country (company_id, country_code)

You may want to read this article in my blog about the difference between entity-relationship model and its relational implementation:

• What is entity-relationship model?