SQL join over five tables

Question

I have five tables:

models:             id, name, specification
models_networks:    id, model_id, network_id
networks:           id, name, description
countries_networks: id, country_id, network_id
countries:          id, countryName, etc, etc

• the models table is connected to the networks table via models_networks with a many to many relation.
• the networks table is connected to the countries table via countries_networks with a many to many relation

I need to do the following query, but I'm stuck:

Select all the models that will work in a specific country.

e.g.: say France has two networks. PigNetwork and CowNetwork. I want to get all the models that work on PigNetwork or CowNetwork, basically any that work in that country one way or the other.

If I've made myself clear, can someone help with the JOIN query please? I've only ever gone as far as joining two tables before. Thanks.

Answer 1

SELECT  m.id
FROM    model m
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    model_networks mn
        JOIN    countries_networks cn
        ON      cn.network_id = mn.network_id
                AND cn.country_id = @code_of_france
        WHERE   mn.model_id = m.id
        )

This is efficient since it returns a model right that moment it finds the first suitable network.

Make sure you have the following UNIQUE indexes:

model_networks (model_id, network_id)
country_network (country_id, network_id)

Answer 2

SELECT models.id, models.name, models.specifications JOIN models_networks ON models.id=models_networks.model_id WHERE models_networks.networks_id IN (1,2)

(replace 1,2 with your network ids )

Doesn't look like you need two joins if you just want the models. You only need more joins if you don't know the network ids or if you need columns out of the other tables. The syntax for that is the same though. You just start with JOIN <table> ON <field>=<field> before the WHERE statement

Answer 3

Something along the lines of this should work...

SELECT M.Name As ModelName FROM Countries C
INNER JOIN Countries_Networks CN
ON C.CountryId = CN.CountryId
INNER JOIN Networks N
ON CN.NetworkId = N.NetworkId
INNER JOIN ModelNetworks MN
ON MN.NetworkId = N.NetworkId
INNER JOIN Model M
ON M.ModelId = MN.ModelId
WHERE C.CountryName = 'FRANCE'

Answer 4

SELECT
  m.name AS model_name,
  c.countryName,
  COUNT(*) AS network_count
FROM
  models                        AS  m
  INNER JOIN models_networks    AS mn ON mn.model_id = m.id
  INNER JOIN networks           AS  n ON n.id = mn.network_id
  INNER JOIN countries_networks AS cn ON cn.network_id = n.id
  INNER JOIN countries          AS  c ON c.id = cn.country_id
WHERE
  c.countryName = 'France'
GROUP BY
  m.name,
  c.countryName

Answer 5

select models.id, models.name, models.specification from models inner join models_networks on models.id = models_network.network_id inner join countries_networks on models_network.network_id = countries_networks.network_id where countries_networks.countryName = 'France'