CODEWITHSUNDEEP
sqlmysql
Abdullah Khan
Abdullah Khan

Reputation: 2415

MySQL Multi-table join on IDs

Hey there, I need some help with some MYSQL Joins that I really don't have much idea how to achive, or if MySQL allows it.

I have a some tables setup, post_controller, post_text, post_photo, post_link.

post_controller has all the IDs and the post_text, post_photo, post_link tables can't have an entry unless post_controller has an id entry (foreign keys). I went for this aproach because I have different types of posts and I wanted to retrieve them regardless of the type of post it was so I just use the ID in the master table.

What I want to know is, how do I join the 3 tables with the master table to form a big array of all the posts, lets say I want posts 1 to 20 .. is it possible for me to join each type of post from each table like so.

post_contoller

id  userid   type   time
1      2      1     00:00
2      4      1     00:00
3      5      2     00:00
4      23     2     00:00
5      8      3     00:00


post_text

id   content       title
1   {sometext}   {sometext}
2   {sometext}   {sometext}


post_photo

id   content       hash
3   {sometext}   {somehash}
4   {sometext}   {somehash}


post_link

id   content      desc        link
5   {sometext}  {sometext}  {somelink}

End Resulting dataset.

post_photo

id  userid   type   time    content      title     desc      link        hash
1      2      1     00:00  {sometext}  {sometext}
2      4      1     00:00  {sometext}  {sometext}
3      5      2     00:00  {sometext}
4      23     2     00:00  {sometext}                                 {somehash}
5      8      3     00:00                        {sometext}{somelink}

Is this result set possible by joining the 3 tables with respect to the master IDs in the post_controller. If yes, please explain, if no please pose an alternate solution if possible.

Thank you.

Upvotes: 0

Views: 118

Answers (1)

zerkms
zerkms

Reputation: 254896

Yes, it is possible:

   SELECT c.*,
          IFNULL(IFNULL(p.content, t.content), l.content) AS content,
          t.title,
          l.desc,
          l.link,
          p.hash
     FROM post_contoller c
LEFT JOIN post_text t ON t.id = c.id
LEFT JOIN post_photo p ON p.id = c.id
LEFT JOIN post_link l ON l.id = c.id

Upvotes: 1

Related Questions