从关系表加入MySQL

Question

我想从两个表中选择一些数据，但它比我第一次想到的稍微复杂一些。我有两张桌子（content & relations）。

content 
    +----+-----------+--------------------+--------------+ 
    | id | elementId |  title  | field_body | 
    +----+-----------+--------------------+--------------+ 
    | 1 |   1 | test title   | test body | 
    | 2 |   2 | another title  | another body | 
    | 3 |   3 | category 10  | null   | 
    | 4 |   4 | sub category 20 | null   | 
    +----+-----------+--------------------+--------------+ 


    relations 
    +----+----------+----------+-----------+ 
    | id | sourceId | targetId | sortOrder | 
    +----+----------+----------+-----------+ 
    | 1 |  2 |  3 |   1 | 
    | 2 |  2 |  4 |   2 | 
    +----+----------+----------+-----------+ 

的方式表是设置如下， 我创建我的网站的“物品”是由以下部分组成：

• 文章标题
• 文章正文
• 文章主类
• 文章子类别

我需要获取每一篇文章，并从表content

因此，例如，它会带回该行相关的主副类别 - >

another title, another body, category10, sub category 20

因为在关系表源id为2，链接到文章的elementId，targetId是与文章关联的类别的ID。

解决此问题的最佳方法是什么？我已经尝试了几种不同的查询的得到这个工作，但它似乎并不可能

Answer 1

我认为这是一个错误的乌尔关系表中的数据，这可能是基于邻接表型号：

ID为== 2记录必须具有的sourceID场== 3：

relations 
    +----+----------+----------+-----------+ 
    | id | sourceId | targetId | sortOrder | 
    +----+----------+----------+-----------+ 
    | 1 |  2 |  3 |   1 | 
    | 2 |  3 |  4 |   2 | 
    +----+----------+----------+-----------+ 

之后ü可以试试这个SQL：

select 
c.id, 
c.title as article_title, 
c.field_body as article_body, 
c1.title as article_main_category, 
c2.title as article_sub_category 
from 
content c 
join relations as r1 on r1.sourceId = c.elementId 
join content as c1 on c1.elementId = r1.targetId 
join relations as r2 on r2.sourceId = c1.elementId 
join content as c2 on c2.elementId = r2.targetId 

如果r elations表数据是确定的，而且中将sortOrder场是有道理的，那么我们可以试试这个SQL查询：

select 
c.id, 
c.title as article_title, 
c.field_body as article_body, 
c1.title as article_main_category, 
c2.title as article_sub_category 
from 
content c 
join relations as r1 on r1.sourceId = c.elementId and r1.sortOrder = 1 
join content as c1 on c1.elementId = r1.targetId 
join relations as r2 on r2.sourceId = c.elementId and r2.sortOrder = 2 
join content as c2 on c2.elementId = r2.targetId