Mysql – How to design this Draft Table and select from it

MySQL

I've asked the same Question before and deleted it on a different subject, This one has the same design in it

This is my Query: https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/1 with the Table Design.

I'm trying to display the drafts content using the provided Query:

SELECT t.title, p.content, p.version
FROM drafts d
INNER JOIN titles t ON t.id = d.tp_id AND d.t = 1
INNER JOIN posts p ON p.id = d.tp_id AND d.t = 2
WHERE user_id = 1

I'm trying to use INNER JOIN to prevent the not-existing titles and posts from appearing, But I got no results at all,

The result I expect is:

title     content    version
TheTitle  null       null
TheTitle  Content1   1
TheTitle  Content2   2
TheTitle  Content3   3
TheTitle  Content4   4
TheTitle  Content5   5
TheTitle  Content6   6
TheTitle  Content7   7

The first row is from the table titles which has nothing to do with content, version, And all the rows after it is from the table posts,

Also the table posts is linked with the table titles using the column tid, That How I'm supposed to get the title value in rows > 1

Is there a better approach to make this draft? And How do I select the results from it as intended?

Another Queries I tried:

https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/4

Best Answer

The following gets the desired result on MySQL 5.7:

SELECT t.title t, null pc, null pv
FROM drafts d
 JOIN titles t ON t.id = d.tp_id AND d.t = 1
 where d.user_id = 1
union
SELECT (SELECT t.title t
      FROM titles t where t.id = p.tid) t,
   p.content pc, p.version pv
FROM drafts d join posts p
on p.id = d.tp_id AND d.t = 2
where d.user_id = 1

https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/17

Related Question