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

Question

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 1

In general - you can't. Structure you've described is called supertype/subtype with discriminator (if you google for it, you can find some descriptions on Google Books), and while it's easy to draw it in ER diagram, it's difficult to use in a real database. If you can, consider switching to other form of implementing discriminators, specifically including all of the fields in one table. If you are unable to change the structure, you have to take care of the condition inside a programming language and make two queries.

Answer 2

As leafnode suggested already, it would be better to change your table structure. Especially considering the fact you have duplicate data (type is declared both in the feedtable and in the subtables).

I'd suggest either dropping the feeds table or mapping everything to one table (with nullable columns). In case you want the feeds to be ordered by ID (as I assume by looking at your desired result), the latter would be the easiest way to implement this.

Answer 3

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

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 ;