0

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

  • 1
    It looks logical to me that you have no results. You're asking for drafts having t = 1 and t = 2 in the same record. Impossible. – Gerard H. Pille Apr 2 '18 at 0:25
  • @GerardH.Pille I will give a try to make the second INNER JOIN a derived query to solve this problem. . . After trying, it doesn't seem to get results either. – Toleo Apr 2 '18 at 11:43
  • I think your design is flawed, having two records for a single draft. The draft record could have had a t_id and a p_id iso. tp_id + t. – Gerard H. Pille Apr 2 '18 at 14:39
  • @GerardH.Pille Is it possible to provide me witth the best design for it? Because i built it to be compact using the tp_id. – Toleo Apr 2 '18 at 15:11
  • 1
    If a draft can have only one title, the titles table has no reason of being. Just add the title column to the drafts table. Now you can trop the tid column from the posts table too. – Gerard H. Pille Apr 2 '18 at 17:56
1

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

1
With ts (u,t) as (
SELECT d.user_id, t.title t
FROM drafts d
 JOIN titles t ON t.id = d.tp_id AND d.t = 1),
ps (u,pc,pv) as (
SELECT d.user_id u, p.content pc, p.version pv
FROM drafts d
Left JOIN posts p ON p.id = d.tp_id AND d.t = 2)
select ts.t, ps.pc, ps.pv from ts
Left outer join ps on ts.u = ps.u /*
Union 
select ts.t, ps.pc, ps.pv from ps
Left outer join ts on ts.u = ps.u */
Where ts.u = 1
and ps.u = 1
Order by ts.t, ps.pc, ps.pv

See [ https://www.db-fiddle.com/f/weourGGnXWj2b6xu7mNfAp/2 ]

  • Your dbfiddle link doesn't work. CTEs are cool, though I don't think MySQL 5.7 (which is the version used in the question's dbfiddle) support CTEs? I think you need MySQL 8 (or MariaDB 10.2) to do that. – dbdemon Apr 2 '18 at 9:43
  • That really would've been awesome to be used, But as dbdemon mentioned, I use MySQL 5.7, Will ask the team to upgrade to MySQL 5.7+ in this case, And I will try to learn the CTE since I have no knowledge about it at all. – Toleo Apr 2 '18 at 11:42
  • @dbdemon fiddle link repaired – Gerard H. Pille Apr 2 '18 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.