Subquery with joins – need help returning rows even if empty – MySQL

okay guys, this is a big one that’s confusing as hell.

I’m creating a forum type dealio: I have an array of Topics and each topic has an array of Posts associated with the Topic. The Topics list page should show the latest Post from that specific Topic. (Each topic is in a category as well)

Topic Latest post
Check out my page John Smith - 10:00 PM

MYSQL
select `ct`.*, `a`.`username`, `x`.* from `community-topics` as `ct` 
inner join `community-categories` as `cc` on `cc`.`communityCategoryId` = `ct`.`refCategoryId` 
inner join `authentication` as `a` on `a`.`userId` = `ct`.`refCreatedBy` 
inner join (
    select `a2`.`username` AS lastPostUsername, `cp`.`createdAt` AS lastPostCreatedAt, 
    `cp`.`body`, `cp`.`refTopicId` from `community-posts` as `cp` 
    inner join `authentication` as `a2` on `a2`.`userId` = `cp`.`refCreatedBy`
    order by `cp`.`createdAt` desc limit 1
) as x on `x`.`refTopicId` = `ct`.`communityTopicId`
where `cc`.`name` = 'general' order by `ct`.`createdAt` desc

This query actually works how I need it to. The only issue is that a row won’t return if the subquery is empty. I still want to return all of the data outside of the subquery even if the subquery is null / false / empty. I’ve tried using IFNULL / ISNULL, but I don’t think I’m using it right with the additional joins in the subquery.

if I have 2 topics, 1 topic with 5 posts, 1 topic with 0 posts, only the topic with 5 posts will show up and the topic with 0 posts won’t show up because the subquery returns empty.

If the row is empty, I’d like to return something to the front end user showing something like “Sorry, no recent posts” or something along those lines.

Any help would be amazing!

Answer

You want a left join if you want to keep rows even when there is no match:

select `ct`.*, `a`.`username`, `x`.*
from `community-topics` `ct` inner join
     `community-categories` `cc`
      on `cc`.`communityCategoryId` = `ct`.`refCategoryId` inner join 
      `authentication` `a`
      on `a`.`userId` = `ct`.`refCreatedBy` left join
      (select `a2`.`username` AS lastPostUsername, `cp`.`createdAt` AS lastPostCreatedAt, 
             `cp`.`body`, `cp`.`refTopicId`
       from `community-posts` as `cp` left join
            `authentication` as `a2`
             on `a2`.`userId` = `cp`.`refCreatedBy`
       order by `cp`.`createdAt` desc limit 1
      )  x
      on `x`.`refTopicId` = `ct`.`communityTopicId`
where `cc`.`name` = 'general'
order by `ct`.`createdAt` desc;

I would also recommend that you remove all the backticks. They just make the query harder to write and to read.

EDIT:

If I understand your comment:

select `ct`.*, `a`.`username`, `x`.*
from `community-topics` `ct` inner join
     `community-categories` `cc`
      on `cc`.`communityCategoryId` = `ct`.`refCategoryId` inner join 
      `authentication` `a`
      on `a`.`userId` = `ct`.`refCreatedBy` left join
      (select `a2`.`username` AS lastPostUsername, `cp`.`createdAt` AS lastPostCreatedAt, 
             `cp`.`body`, `cp`.`refTopicId`,
             row_number() over (partition by refTopicId order by cp.created_at desc) as seqnum
       from `community-posts` as `cp` left join
            `authentication` as `a2`
             on `a2`.`userId` = `cp`.`refCreatedBy`
      )  x
      on `x`.`refTopicId` = `ct`.`communityTopicId` and seqnum = 1
where `cc`.`name` = 'general'
order by `ct`.`createdAt` desc;