I have a table named posts and another table named attachments...

A post can have many attachments...

So, I create a middle table called post_attachment..

How to get the list of posts data including the attachments data in A SINGLE QUERY?


Please refer below to make it clearer to understand...

Below are the table structure:


posts table have 4 columns:

  1. id
  2. title
  3. body_text


attachments table have 3 columns:

  1. id
  2. filename
  3. file_url


post_attachment table have 2 columns:

  1. post_id
  2. file_id

Below are the example of a post that have multiple attachments

posts table:

enter image description here


attachments table:

enter image description here


post_-attachment table:

enter image description here

In the example above, it tells the post (ID: 1) has 3 attachments which is ID: 1,2 and 3.

So, the question is How to GET the list of posts which will have columns posts.title, attachments.filename, attachments.file_url in A SINGLE QUERY?

1 Answers

forpas On

You must join the 3 tables:

  p.*, a.*
from post p
left join post_attachment pa on pa.post_id = p.id
left join attachments a on a.id = pa.file_id

The LEFT JOIN is needed in case a post does not have any attachments.