php - MySQL 查询问题 - 连接和分组

Question

我以一种更简单的方式重新发布了这个问题，因为我收到了关于它过于具体的评论:

根据以下旅行模型关系，如何选择Region中至少有一个Stop的所有Itineras X，并且仍然让查询返回该Itinerary 的所有Stops，即使其他Stops 位于Regions 除了 X

我正在使用 Php、MySQL 和 Doctrine。

Region HAS MANY Country
Country HAS MANY Area
Area HAS MANY Property
Property HAS MANY Stop 
Itinerary HAS MANY Stop

Answer 1

SELECT i.*
     , sa.*
FROM Itinerary AS i
  JOIN Stop AS sa
    ON i.ItineraryId = sa.ItineraryId
WHERE EXISTS
      ( SELECT *
        FROM Stop AS s
          JOIN Property AS p
            ON p.PropertyId = s.PropertyId
          JOIN Area AS a
            ON a.AreaId = p.AreaId
          JOIN Country AS c
            ON c.CountryId = a.CountryId 
          JOIN Region AS r
            ON r.RegionId  = c.RegionId
        WHERE s.ItineraryId = i.ItineraryId
          AND r.Name = @X
      )