Mysql JOIN的四个表和两个键表 [英] Mysql JOIN of four tables with two key tables

问题描述

我不愿承认这一点，因为我对较复杂的查询缺乏MySQL知识.本质上，我有四个表，其中两个包含要返回的数据，两个是链接数据的关系表.出现Table A只是为Table D.aID提供填充物.

I hate to admit it by my knowledge of MySQL is lacking when it comes to the more complex queries. Essentially I have four tables two of them contain the data I want to return, and two are relational tables linking the data. Table A is present just to provide filler for Table D.aID.

+--------+ +--------+ +--------+ +-----------+ +-----------+
|Table A | |Table B | |Table C | |  Table D  | |  Table E  |
+---+----+ +---+----+ +---+----+ +---+---+---+ +---+---+---+
|aID|name| |bID|name| |cID|name| |dID|aID|bID| |eID|dID|cID|
+---+----+ +---+----+ +---+----+ +---+---+---+ +---+---+---+
| 1 | a_1| | 1 | b_1| | 1 | c_1| | 1 | 1 | 1 | | 1 | 1 | 1 |
+---+----+ | 2 | b_2| | 2 | c_2| | 2 | 1 | 2 | | 1 | 1 | 2 |
           +---+----+ | 3 | c_3| +---+---+---+ +---+---+---+
                      +---+----+

使用这些表创建的关系是:Table A > Table B > Table C.我想要的数据属于Table B > Table C关系.

The relationship created with these tables is: Table A > Table B > Table C. The data I am wanting belongs to the Table B > Table C relationship.

+--------+---------+--------+---------+
|tblB.bID|tblB.name|tblC.cID|tblC.name|
+--------+---------+--------+---------+
|    1   |    a_1  |    1   |   c_1   |
|    1   |    a_1  |    2   |   c_2   |
|    2   |    a_2  |  NULL  |   NULL  |
+--------+---------+--------+---------+

但是，要确保我遵循正确的路径，我需要获取Table A > Table B关系Table C所属的Table B.我意识到，允许重复的name值使我自己变得更加困难，但是与膨胀的表和更简单的查询相比，我宁愿拥有小的表和更复杂的查询.我正在使用的查询是

However to ensure I am following the correct path I need to grab the Table B of the Table A > Table B relationship Table C belongs to. I realize that I am making things much more difficult for myself by allowing for duplicate name values, but I would rather have small tables and more complex queries than bloated tables and simpler queries. The query I am using is

SELECT * FROM `Table E`
LEFT JOIN `Table D` ON (`Table B`.bID = `Table D`.bID)
RIGHT JOIN `Table E` ON (`Table D`.dID = `Table E`.dID))
RIGHT JOIN `Table C` ON (`Table E.cID = `Table C`.cID);

但是到目前为止，它还没有起作用.提交查询后，将返回此错误:

However so far it has not worked. When the query is submitted this error is returned:

ERROR 1066 (42000): Not unique table/alias: 'Table D'

关于如何使它起作用的任何想法?这有可能吗?

Any ideas on how I can get this to work? Is this even possible?

推荐答案

感谢Martin Smith，我得以提出我在此处发布的解决方案.我希望这可以帮助其他人.

Thanks to Martin Smith I was able to come up with the solution I am posting here. I hope this might be able to help someone else.

SELECT  tblB.bID,
    tblB.name,
    tblC.cID,
    tblC.name
FROM Table E 
RIGHT JOIN Table B ON (Table B.bID = Table D.bID)
RIGHT JOIN Table D USING dID
RIGHT JOIN Table C USING cID;

这篇关于Mysql JOIN的四个表和两个键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！