sdfgg45 sdfgg45 - 3 months ago 10
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.

CREATE TABLE product(
id INT auto_increment primary key,
name varchar(255)
);

CREATE TABLE tags(
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:

select
product.name,
tags.tag_name
from product
INNER JOIN product_tag ON product.id = product_tag.product_id
INNER JOIN tags ON product_tag.tag_id = tags.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

Here's the full query:

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

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

Explanation:

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 product.id comes into play.

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

WORKING DEMO

Comments