mysql selecting data poblem

Question

I have two tables, products and product_tags.

products table;

product_id int
name varchar
description text
price decimal(12,2)
status enum
sale int
hit int
date_add datatime
date_update datetime

product_tags table;

tag_id int
product_id int
tag varchar

where the product_tags table has a one to many relation with products, for example:

tag_id | product_id | tag
1       1             tag1
2       1             tag2
3       1             tag3

I want to pull data from a single query. (product and product tags).

Answer 1

Use something like this:

SELECT *
  FROM products p
 INNER JOIN product_tags pt
    ON (p.product_id = pt.product_id)

If you want to fetch all the products regardless of whether they have a tag or not, then use an OUTER JOIN.

SELECT *
  FROM products p
  LEFT OUTER JOIN product_tags pt
    ON (p.product_id = pt.product_id)

If you are trying to combine all of the tags for each product into a single comma-separated list then something like this might work in mysql:

SELECT p.product_id, GROUP_CONCAT(pt.tag SEPARATOR ',')
  FROM products p
  LEFT OUTER JOIN product_tags pt
    ON (p.product_id = pt.product_id)
 GROUP BY p.product_id;

For further documentation of GROUP_CONCAT, see the MySQL Reference Manual