My SQL is only bringing results if there are comments

Question

I have an SQL query that pulls all of my blogs out, with the blog author and it does this for every blog comment corresponding to the comment author.

These 3 tables look like this:

CREATE TABLE `blogs` (
  `uuid` int(255) NOT NULL,
  `title` varchar(1024) NOT NULL,
  `detail` varchar(1024) NOT NULL,
  `user_id` int(255) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `blogs`
    ADD PRIMARY KEY (`uuid`),
    ADD KEY `user_id` (`user_id`);

ALTER TABLE `blogs`
    MODIFY `uuid` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

ALTER TABLE `blogs`
    ADD CONSTRAINT `blogs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` 
    (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION;


CREATE TABLE `blogs_comments` (
  `uuid` int(255) NOT NULL,
  `blog_uuid` int(255) NOT NULL,
  `user_uuid` int(255) NOT NULL,
  `comment` varchar(250) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `blogs_comments`
    ADD PRIMARY KEY (`uuid`),
    ADD KEY `user_uuid` (`user_uuid`),
    ADD KEY `blog_uuid` (`blog_uuid`);

ALTER TABLE `blogs_comments`
    MODIFY `uuid` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `blogs_comments`
    ADD CONSTRAINT `blogs_comments_ibfk_1` FOREIGN KEY (`blog_uuid`) 
    REFERENCES `blogs` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    ADD CONSTRAINT `blogs_comments_ibfk_2` FOREIGN KEY (`user_uuid`) 
    REFERENCES `users` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

CREATE TABLE `users` (
  `uuid` int(255) NOT NULL,
  `username` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `foreName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `surName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `email` text COLLATE utf8_unicode_ci NOT NULL,
  `number` int(11) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_admin` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `users`
    ADD PRIMARY KEY (`uuid`);

ALTER TABLE `users`
    MODIFY `uuid` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

My current query looks like this:

SELECT
    b.uuid AS 'Blog ID', b.title AS 'Blog Title',
    b.detail AS 'Blog Body', u.uuid AS 'Blog Author ID',
    u.username AS 'Blog Author Username',
    (SELECT bc.comment WHERE u.uuid = bc.user_uuid) AS 'Comment',
    (SELECT u.uuid WHERE u.uuid = bc.user_uuid) AS 'Comment Author ID',
    (SELECT u.username WHERE u.uuid = bc.user_uuid) AS 'Comment Author Username'
FROM
    blogs b
INNER JOIN
    blogs_comments bc
ON
    b.uuid = bc.blog_uuid
INNER JOIN
    users u
ON
    b.user_id = u.uuid

This query works fine if there are comments on the blogs. If there is no comments, the blog is not returning. Does anyone have any idea on how I could change this around so it brings out blogs even if there is no comments?

Thanks in advance

Update: My PDO looks like this:

$stmt = (new PDO('dsn', 'user', 'pswd'))->Prepare($sql);
$stmt->execute([], PDO::FETCH_ASSOC);
var_export($stmt->fetchAll()); // Array holds nothing for blogs with no comments

Answer 1

You need a left join, starting with users. However, I think your real confusion is that you need to join to users twice:

SELECT b.uuid AS Blog_ID,
       b.title AS Blog_Title,
       b.detail AS Blog_Body,
       u.uuid AS Blog_Author_ID,
       u.username AS Blog_Author_Username,
       bc.comment AS Comment,
       bc.user_uuid AS Comment_Author_ID,
       ubc.username AS Comment_Author_Username
FROM users u LEFT JOIN
     blogs b
     ON b.user_id = u.uuid LEFT JOIN
     blogs_comments bc
     ON b.uuid = bc.blog_uuid LEFT JOIN
     users ubc
     ON bc.user_uuid = u.uuid;

Notes:

• Do not use single quotes for column aliases. Only use single quotes for string and date constants.
• Do not give your column aliases names that need to be escaped. I replaced the spaces with underscores.
• If you want both the name of the blog user and the comment user, then you need to join to users twice.
• I don't understand why you were using subqueries in the SELECT with no FROM clause.