StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I get mysql to pull in an average rating along with tag search?
text
Body
copied!<p>I am trying to get my head round a complex mysql statement (complex to me anyway!). Basically, I need to return a list of all products from within the products table with an extra return value (their respective star rating (rating table) which must be calculated on an average of the total of all ratings for that product).</p> <p>The sql statement must also include the ability to filter the products based upon multiple 'tag' words , e.g search for all products that are linked (through product_tags table to the tags table) to words specified when constructing the sql statement. So, if I needed to retrieve products that have the tags 'red' and 'white', the results would return products 1 and 3 with their respective average rating.</p> <p>Below is a sql dump of the sample tables.</p> <pre><code>DROP TABLE IF EXISTS `product_tags`; DROP TABLE IF EXISTS `rating`; DROP TABLE IF EXISTS `tags`; DROP TABLE IF EXISTS `products`; CREATE TABLE IF NOT EXISTS `products` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) CHARACTER SET latin1 NOT NULL, `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ; INSERT INTO `products` (`product_id`, `product_name`, `date_added`) VALUES (1, 'first item', '2011-05-26 21:56:06'), (2, 'second item', '2011-05-26 21:56:06'), (3, 'third item', '2011-05-26 21:56:06'); CREATE TABLE IF NOT EXISTS `product_tags` ( `product_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL, KEY `product_id` (`product_id`), KEY `tag_id` (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `product_tags` (`product_id`, `tag_id`) VALUES (1, 4), (1, 1), (1, 8), (2, 3), (2, 9), (3, 8), (3, 7), (1, 6), (2, 5), (3, 2), (3, 10); CREATE TABLE IF NOT EXISTS `rating` ( `product_id` int(11) NOT NULL, `rating` float NOT NULL, KEY `product_id` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `rating` (`product_id`, `rating`) VALUES (1, 5), (1, 0), (2, 3), (2, 4.5), (1, 2), (2, 4); CREATE TABLE IF NOT EXISTS `tags` ( `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tag_name` varchar(50) NOT NULL, PRIMARY KEY (`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ; INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES (1, 'red'), (2, 'green'), (3, 'yellow'), (4, 'cyan'), (5, 'blue'), (6, 'pink'), (7, 'purple'), (8, 'grey'), (9, 'black'), (10, 'white'); ALTER TABLE `product_tags` ADD CONSTRAINT `product_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `product_tags_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product_tags` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE `rating` ADD CONSTRAINT `rating_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload