mysql - 编写具有多个连接的 MySQL 语句

Question

我有 4 个表，它们是:

job, community, state, and region.  

它们的结构如下:

job:
columns:
  id
  community_id
relations:
  community:  local_key: community_id, foreign_key: id

community:
columns:
  id
  state_id
relations: 
  state: local_key: state_id, foreign_key: id

state:
columns:
  id
  name
  region_id
relations:
  region: local_key: region_id, foreign_key: id

region:
columns:
  id
  name

现在，我需要一个查询:

get all the jobs with matching communities: i.e.: j.community_id = c.id

then, from those matches, get all of the jobs in communities with a state region_id = "1"

我做得很好，拉动状态，但我在尝试拉动该地区时遇到困难。我做到了这一点:

SELECT j.id
FROM job j
INNER JOIN community c
ON j.community_id = c.id
WHERE c.state_id = 35

我什至不确定我的表是否设置正确以便检索此信息。任何帮助我度过难关的帮助都将不胜感激。

Answer 1

试试这个，我想这就是你想要的。

SELECT
    r.name AS regionname,
    s.name AS statename
FROM
    job j
LEFT JOIN
    community c ON c.id = j.community_id
LEFT JOIN
    state s ON s.id = c.state_id
LEFT JOIN
    region r ON r.id = s.region_id
WHERE
    r.id = 1