CODEWITHSUNDEEP
sqlmysqljoin
Chris J Allen
Chris J Allen

Reputation: 19207

SQL join over five tables

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

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.

Upvotes: 2

Views: 6124

Answers (5)

Svante Svenson
Svante Svenson

Reputation: 12478

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'

Upvotes: 1

Tomalak
Tomalak

Reputation: 338406

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

Upvotes: 3

pjp
pjp

Reputation: 17639

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'

Upvotes: 3

Cfreak
Cfreak

Reputation: 19319

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425823

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)

Upvotes: 2

Related Questions