php - SQL通过各种ID选择各种名称

Question

我有这 3 个表:

主表:

  ID | OtherStuff1 | OtherStuff2 | IdProvince | IdTown 
-----+-------------+-------------+------------+--------
   1 |   Stuff1    |   Stuff2    |      1     |   1    

省表:

ID | ProvinceName 
---+--------------
 1 |  ProvName1   

城镇表:

ID |   TownName   
---+--------------
 1 |   TwName1  

很明显，主表与省表和镇表有外部关系。

我想要的结果是这样的:

ID | OtherStuff1 | OtherStuff2 | IdProvince |   IdTown   
---+-------------+-------------+------------+------------
 1 |   Stuff1    |   Stuff2    | ProvName1  |   TwName1  

我想显示主表，但使用名称而不是 ID。

我查找了其他问题并尝试了这个问题:

    SELECT *
        FROM main AS a 
        INNER JOIN province AS b
        INNER JOIN town AS c
        WHERE a.IdProvince = b.ProvinceName
        AND a.IdTown = c.TownName 

    AND Id=1; # this is added by me because I want to filter by id on the
              # main table, I don't want all the rows, just one filtered by ID.

还有这个:

 SELECT *
       FROM main AS a, 
       INNER JOIN province AS b ON a.IdProvince = b.ProvinceName
       INNER JOIN town AS c ON a.IdTown = c.TownName
       WHERE Id=1;

另外我希望查询可以很容易地使用如果我有同样的问题，但不是主表和 2 个表，我有主表和 4 个表我也可以使用它只是添加例如更多“INNER JOINS”？

我正在尝试执行此操作，但无法正确执行，我每次都将此 Sql 弄错，而且我找不到执行此操作的正确方法，有什么想法吗？

我正在使用 PDO 并在网页上显示它。

"AND Id=1"--> 这样使用 --> "AND Id=:filterID".

希望我的解释是正确的!

有什么问题尽管问，我会尽快回复!

提前致谢!

-------------------------------------------- - - - - - - - - 编辑 - - - - - - - - - - - - - - - - - ------------------------------

我已经尝试了我得到的3个答案，但它不起作用，我不知道为什么，我会添加图像备份以便你看到。

这个没有错误，但我没有得到任何信息

SELECT a.Id, OtherStuff1, OtherStuff2, IdProvince, IdTown
 FROM main AS a 
 INNER JOIN province AS b 
 INNER JOIN town AS c 
   WHERE a.IdProvince = b.ProvinceName 
   AND a.IdTown = c.TownName 
AND a.Id=1

This is the result

这个是一样的，没有返回信息，但无论如何我从其他表中得到了我不需要的额外列。

SELECT * 
 FROM main AS a ,province AS b , town AS c 
  WHERE a.IdProvince = b.ProvinceName 
  AND a.IdTown = c.TownName 
AND a.Id=1

This is the second result

我也试过这个，但我无法让它工作，总是显示错误

SELECT *
   FROM 
      (main AS a 
      INNER JOIN province AS b ON a.IdProvince = b.ProvinceName) AS d
      INNER JOIN town AS c ON d.IdTown = c.TownName
   WHERE 
      Id=1

我不能发布超过 2 张照片，因为我没有足够的声誉，但我将错误粘贴在这里:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'd
      INNER JOIN town AS c ON d.IdTown = c.TownName
   WHERE
      Id=1 LI' at line 4

如果您提出疑问或怀疑，我确实将信息放在了表格中，每行 1 行，所以里面有一些东西，但出于某种原因，它没有给我返回该信息。

-------------------------------------------- --------最后编辑---------------------------------------- ----------------------

这 3 个代码经过一些编辑尝试后对我有用，这里是 3 个代码，因此可能阅读本文的人会觉得这很有帮助。

SELECT a.Id, OtherStuff1, OtherStuff2, ProvinceName, TownName
    FROM main AS a 
    INNER JOIN province AS b ON a.IdProvince = b.Id
    INNER JOIN town AS c ON a.IdTown = c.Id 
    WHERE a.Id=1;

---

SELECT a.Id, OtherStuff1, OtherStuff2, ProvinceName, TownName 
    FROM main AS a 
    INNER JOIN (province AS b, town AS c) 
    ON (a.IdProvince = b.Id AND a.IdTown = c.Id) 
    WHERE a.Id=1;

---

SELECT a.Id, OtherStuff1, OtherStuff2, ProvinceName, TownName 
    FROM main AS a ,province AS b , town AS c 
    WHERE a.IdProvince = b.Id
    AND a.IdTown = c.Id 
    AND a.Id=1;

非常感谢帮助过我的人!

Answer 1

嗯，你很接近，只是你的连接条件不对:

SELECT  main.ID, OtherStuff1, OtherStuff2, ProvinceName, TownName
    FROM main AS a 
    INNER JOIN province AS b ON a.IdProvince = b.Id
    INNER JOIN town AS c ON a.IdTown = c.Id 
    WHERE main.Id=1;