sdfgg45 sdfgg45 - 1 year ago 54
MySQL Question

How to match on several rows using left inner join on mysql

I have the following scenario: Not sure how to explain it better but ill try to give some sql details and a valid scenario.

I have a table that have products,one that keeps the product tags, and one that saves the ones added to a product. I can add several tags to one product.

id INT auto_increment primary key,
name varchar(255)

id INT auto_increment primary key,
tag_name varchar(255)

CREATE TABLE product_tag(
id INT auto_increment primary key,
tag_id INT,
product_id INT

The sql query using inner join gives me:

from product
INNER JOIN product_tag ON = product_tag.product_id
INNER JOIN tags ON product_tag.tag_id =

The result gives me:

Product 1 | Tag1
Product 1 | Tag2
Product 1 | Tag3
Product 2 | Tag1
Product 2 | Tag3

I want to make a query that check if product 1 have tag1,tag2 and tag3 have all 3 tags attached. Imagine the user on front end selects all tree tags, then i want to show the product that have all tree tags refered to it. But i get 3 rows and each row contain only one row in this example. What would be the proper way to do this.

Answer Source

Here's the full query:

FROM product_tag PT 
INNER JOIN tags T ON PT.tag_id =
INNER JOIN product P ON PT.product_id =
WHERE T.tag_name IN ('Tag1','Tag2','Tag3')
GROUP BY PT.product_id

This query will give output only those product id and names which are involved in all the three tags given.


Only IN doesn't guarantee that a single product holds all the three tags given.

Since you want result for each product that's why GROUP BY comes into play.

Later HAVING COUNT( = 3 is used to filter out those products only which hold all the three tags given.