CODEWITHSUNDEEP
sqlmysqlstored-procedures
reedcat
reedcat

Reputation: 13

How to retrieve products by tags (SQL question)

I have two MySQL tables:

each product row can have one ore more rows in table tag joined by *product_id* field. Question: how do I select products by *tag_name* and other attributes? Example: I need to select all books cheaper than $10 and tagged 'foreign' and 'sci-fi'

I know how to select products with one tag (join product and tag by *product_id* and set where clause) I know how to select products with more than one tag using a stored procedure, however queries take very long time to run. Is there a way to avoid stored procedure and still get the result?

Update: StackOverflow does something like this when you click "Related Tags" on the right hand side bar. Now, I don't know what architechture their DB has, but the result is fairly quick selection of all questions tagged with multiple tags.

Upvotes: 1

Views: 606

Answers (3)

Nik
Nik

Reputation: 4075

try,

SELECT p.* FROM products p 
INNER JOIN tag t ON p.id = t.product_id 
WHERE p.price < 10 AND p.id IN( SELECT product_id FROM tag  WHERE  t.tag_name = 'foreign' OR t.tag_name = 'sci-fi' GROUP BY product_id HAVING COUNT(product_id) = 2)

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166506

This will return any product with price less than 10, and with tag foreign or sci-fi

How about something like

SELECT  p.*
FROM    product p
WHERE   p.price < 10
AND     EXISTS  (
                    SELECT  1
                    FROM    tag t
                    WHERE   p.id = t.product_id
                    AND     t.tag_name IN ('foreign','sci-fi')
                )

This will ensure that it has both tags, assuming that tags has primary key product_id and tag_name

SELECT  p.*
FROM    product p
WHERE   p.price < 10
AND     EXISTS  (
                    SELECT  1
                    FROM    tag t
                    WHERE   p.id = t.product_id
                    AND     t.tag_name IN ('foreign','sci-fi')
                    HAVING COUNT(1) = 2
                )

Upvotes: 1

Kangkan
Kangkan

Reputation: 15571

It seems to be simple query, if I have the problem right.

What I see is:

Select Product.* 
from Product, Tag
where Product.id = Tag.product_id
and Product.price<$10
and tag.tag_name in ('foreign', 'sci-fi')

Upvotes: 0

Related Questions