SQL 连接五个表

Question

我有五个表:

models:             id, name, specificationmodels_networks:    id, model_id, network_idnetworks:           id, name, descriptioncountries_networks: id, country_id, network_idcountries:          id, countryName, etc, etc

• models 表通过具有多对多关系的models_networks 连接到networks 表。
• networks 表通过具有多对多关系的countries_networks 连接到countries 表

我需要执行以下查询，但我卡住了:

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

例如:假设法国有两个网络。 PigNetwork 和 CowNetwork。我想获得在 PigNetwork 或 CowNetwork 上运行的所有模型，基本上任何在那个国家以某种方式运行的模型。

如果我说清楚了，有人可以帮忙处理 JOIN 查询吗？我以前只做过加入两张 table 的事情。谢谢。

Answer 1

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