How can I get mysql to pull in an average rating along with tag search?

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).

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.

Below is a sql dump of the sample tables.

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, 开发者_如何学Go'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;

SELECT  
    p.product_id
  , p.product_name
  , p.date_added
  , ( SELECT AVG(r.rating) 
      FROM rating r
      WHERE r.product_id = p.product_id
    )
    AS avg_rating 
FROM
  products p
    JOIN
  product_tags pt
      ON pt.product_id = p.product_id
    JOIN
  tags t
      ON t.tag_id = pt.tag_id
WHERE
    t.tag_name IN ('red','white')
GROUP BY
    p.product_id

As a side note, it's better to use singular for table names. product, tag, product_tag (like rating which is already singular) and not plural as you do: products, etc.

SELECT `products`.`product_id`, `product_name`, `date_added`,
       AVG(`rating`) avg_rating,
       GROUP_CONCAT(`tags`.`tag_name`) all_tags
  FROM `products`
  JOIN `product_tags` ON `products`.`product_id` = `product_tags`.`product_id`
  JOIN `tags` ON `product_tags`.`tag_id` = `tags`.`tag_id`
  LEFT JOIN `rating` ON `products`.`product_id` = `rating`.`product_id`
 WHERE `tags`.`tag_name` in (?)
 GROUP BY `products`.`product_id`

select 
  p.product_id
  , p.product_name
  , group_concat(distinct tag_name) as tags
  , ifnull(avg(r.rating),'no rating') as avg_rating 
from products p
left join rating r on (r.product_id = p.product_id)
inner join product_tags pt on (pt.product_id = p.product_id)
inner join tags t on (t.tag_id = pt.tag_id)
where tag_name in ('red','white')
group by p.product_id

Result:

1, 'first item', 'red', '2.33333333333333'
3, 'third item', 'white', 'no rating'