Mysql选择具有相同表的行不在where子句中

Question

我有 2 个这样的表:

表一

+----+-----------+--------------------+| ID | Postcode4 |     woonplaats     |+----+-----------+--------------------+|  1 |      9044 | Beetgum            ||  2 |      9045 | Beetgummermole     ||  3 |      1028 | Amsterdam          ||  4 |      1029 | Amsterdam          ||  5 |      1030 | Amsterdam          |+----+-----------+--------------------+

表2

+----+------+-------+| ID | 4PP  | Regio |+----+------+-------+|  1 | 9044 |     2 ||  2 | 9045 |     2 ||  3 | 1028 |     4 ||  4 | 1029 |     4 ||  5 | 1030 |     4 |+----+------+-------+

我首先要选择 Beetgum 所在的地区，在本例中为地区 2。然后我希望表 2 中的所有其他 4PP 具有相同的地区 (2)。所以查询的结果必须是 9044,9045

Answer 1

这是一个合并的:)

SELECT t2.*
FROM Table1 
INNER JOIN Table2 ON Table2.4PP = Table1.Postcode4
INNER JOIN Table2 t2 ON t2.Regio = Table2.regio
WHERE Table1.woonplaats = 'Beetgum'

fiddle :http://sqlfiddle.com/#!2/347f3/3