php - 如何在多表到一个表之间创建一个连接语句？

Question

首先，我有 4 个表和列，例如

1. 提要(id、类型、type_id)
2. feeds_normals(id、类型、内容)
3. feeds_links(id、类型、标题、链接)
4. feeds_youtubes(id、类型、标题、链接、描述、图片)
5. feeds_photos(id、类型、链接)

“feeds type_id”表是法线、链接、youtube、照片的匹配/链接“id”

和

“feeds 类型”表正在使用已识别的应该连接的表

例如:

feeds:
id: 1, type: "normal", type_id: 1
id: 2, type: "link", type_id: 1

feeds_normals:
id: 1, type: "normal", content: "this is a test"

feeds_links:
id: 1, type: "link", title: "This is a title", link: "http://yahoo.com"

结果:

id: 1, type: "normal", content: "this is a test", title: NULL, link: NULL
id: 2, type: "link", content: NULL, title: "This is a title", link: "http://yahoo.com"

最后

这种情况下，SQL语句怎么写？

Answer 1

这里有问题，可能是示例或实际数据。以下是父类(super class)型/子类型模型通常的样子:

匹配的数据示例是:

Feeds:
FeedId: 1, type: "normal"
FeedId: 2, type: "link"

Feeds_Normals:
FeedId: 1, content: "this is a test"

Feeds_Links:
FeedId: 2, title: "This is a title", link: "http://yahoo.com"

请注意，子类型表中的 FeedID 与父类(super class)型中的匹配。子类型表中的 Type 字段是可选的——它允许检查约束来强制类型不会在子类型表中混合。

查询看起来像这样:

select
      f.FeedID
    , n.Content      as NormalsContent
    , y.Title        as YouTubeTitle
    , y.Link         as YouTubeLink
    , y.Description  as YouTubeDescription
    , y.Image        as YouTueImage
    , k.Title        as LinksTitle
    , k.Link         as LinksLink
from Feeds              as f
left join Feeds_Normals as n on n.FeedId = f.FeedId
left join Feeds_Links   as k on k.FeedId = f.FeedId
left join Feeds_YouTube as y on y.FeedId = f.FeedId ;