Zeu Zeu -4 years ago 61
MySQL Question

How to make a join statement between multi table to one table?

First, I have 4 table and columns such as


  1. feeds (id, type, type_id)

  2. feeds_normals (id, type, content)

  3. feeds_links (id, type, title, link)

  4. feeds_youtubes (id, type, title, link, description, image)

  5. feeds_photos (id, type, link)



The table of "feeds type_id" is match/linkup "id" of normals, links, youtubes, photos

And

The table of "feeds type" is using the identified which table should be joined

For example:

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"


Result:

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"


Finally

In this case, how to write by SQL statement?

Answer Source

Something is wrong here, it may be the example or actual data. Here is how a super-type/subtype model usually looks like:

alt text

The matching data-example would be:

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"

Note that the FeedID in a subtype table matches the one in the super-type. The Type field in subtype tables is optional -- it allows for a check constraint to enforce that types do not get mixed in a subtype table.

The query would look something like:

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 ;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download