view – How to join three tables as one in MySQL?


I think this question is pretty basic, but despite searching, I failed to find a solution that works for me.

I have three tables that have various data, that I want to “merge” into one resulting table, where I can apply some filters.

Here are the table details for each of them:

messages

  • received (datetime)
  • message (text)
  • feedback_id (Int – foreign key to feedbacks table)

feedback_notes

  • message (text)
  • created (datetime)
  • feedback_id (FK)

feedback_events

  • event (varchar)
  • executed (datetime)
  • author_id (FK)
  • feedback_id (FK)

My issue here is to create a view that will have feedback_events.executed, feedback_notes.created and message.received under the same column, that I want to be able to filter (order mostly, to have all those three tables order by chronological events).

The view would be composed of

Feedback_views:

  • executed (alias for feedback_events.executed, feedback_notes.created and messages.received)
  • message (for messages and feedback_notes)
  • feedback_id
  • event
  • author_id
  • original_table (or a way to know where this belongs to, between “messages”, “feedback_notes” and “feedback_events”)

Both the feedback_views.executed” and “feedback_views.original_table” are tricky for me. How can I do that?

Thank you in advance!