я хочу выбрать те вопросы, которые понравились конкретному пользователю (не ключевое слово sql) имя пользователя

таблицы следующие

tbl_users

CREATE TABLE `queryit`.`tbl_users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(55) NOT NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_status` ENUM ("active","inactive","delete") DEFAULT "active",
  `signup_at` datetime DEFAULT current_timestamp(),
  `user_role` ENUM ("admin","user") DEFAULT "user",
  `s_question` TINYTEXT NOT NULL,
  `s_answer` varchar(30) NOT NULL,
  CONSTRAINT `user_pk` PRIMARY KEY (`user_id`),
  CONSTRAINT `user_uk` UNIQUE (`email`, `username`)
) ENGINE=InnoDB;

tbl_questions

CREATE TABLE `queryit`.`tbl_questions` (
    question_id INT AUTO_INCREMENT,
    question_title VARCHAR(255) NOT NULL,
    question_body TEXT NOT NULL,
    question_posted_at DATETIME DEFAULT current_timestamp(),
    user_id INT NOT NULL,
    CONSTRAINT tbl_question_pk PRIMARY KEY (`question_id`),
    CONSTRAINT tbl_question_fk1 FOREIGN KEY (`user_id`) REFERENCES `queryit`.`tbl_users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE = InnoDB;

tbl_questions_likes

CREATE TABLE  `tbl_question_likes` (
    `question_like_id` INT AUTO_INCREMENT,
    `like` INT NOT NULL DEFAULT 0,
    `dlike` INT NOT NULL DEFAULT 0,
    `question_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    CONSTRAINT `tbl_question_likes_pk` PRIMARY KEY (`question_like_id`),
    CONSTRAINT `tbl_question_likes_fk1` FOREIGN KEY (`question_id`) REFERENCES `queryit`.`tbl_questions`(`question_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `tbl_question_likes_fk2` FOREIGN KEY (`user_id`) REFERENCES `queryit`.`tbl_users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;

Я пытаюсь этот запрос

SELECT
    `tbl_questions`.`question_id`, 
    `tbl_questions`.`question_title`,
    COALESCE(SUM(`tbl_question_likes`.`like`),0) AS `likes`
FROM
    `tbl_users`
RIGHT JOIN `tbl_questions` ON `tbl_users`.`user_id` = `tbl_questions`.`user_id`
LEFT JOIN `tbl_question_likes` ON `tbl_questions`.`question_id` = `tbl_question_likes`.`question_id`
WHERE `tbl_users`.`user_status`='active' AND `tbl_question_likes`.`user_id` = (SELECT `user_id` FROM `tbl_users` WHERE `tbl_users`.`username` = 'sagar')
GROUP BY
    `tbl_questions`.`question_id`
ORDER BY COALESCE(SUM(`tbl_question_likes`.`like`),0) DESC
LIMIT 10

Приведенный выше запрос возвращает вопросы, которые понравились конкретному пользователю, но возвращает количество лайков для каждого вопроса 1 (но у вопроса больше 1 лайка).

|источник

0