Alko Alko - 5 months ago 8
SQL Question

Php, MySql join 3 tables and count total

I have 3 joined tables, and trying to pull records from it, but for some reason not able to achieve it. I have created SQL Fiddle here: http://sqlfiddle.com/#!9/9eeaf/1/0

What I'm getting now is only records that have COUNT of 1 > are shown, but I need to show all of them regardless of count being 0

CREATE TABLE `questions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`question` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `questions` (`id`, `question`) VALUES
(1, 'How do you find our site?'),
(2, 'What is your favoutite color');



CREATE TABLE `options` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`question_id` int(11) NOT NULL,
`value` varchar(250) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `options` (`id`, `question_id`, `value`) VALUES
(1, 1, 'Hard'),
(2, 1, 'Easy'),
(3, 1, 'Very Easy'),
(4, 1, 'Piece of cake'),
(5, 1, 'Green'),
(6, 1, 'Blue'),
(7, 1, 'Red'),
(8, 1, 'Black');


CREATE TABLE `votes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`option_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_option` (`option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `votes` (`id`, `option_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 4),
(5, 5);

SELECT
q.question,
o.value,
IFNULL(COUNT(DISTINCT v.option_id), 0) AS total
FROM
questions AS q
LEFT JOIN OPTIONS AS o
ON o.question_id = q.id
LEFT JOIN votes AS v
ON v.option_id = o.id
GROUP BY v.option_id;

Answer

I think your looking for this:

SELECT
  q.question,
  o.value,
  IFNULL(COUNT(v.option_id), 0) AS total 
FROM
  options AS o
  LEFT JOIN votes AS v 
    ON o.id = v.option_id
  JOIN questions as q
  ON o.question_id = q.id
 GROUP BY o.id;

Fiddle: http://sqlfiddle.com/#!9/9eeaf/26

Comments