CODEWITHSUNDEEP
mysqlselectjoin
Chris
Chris

Reputation: 1058

Searching multiple rows in select with left join

I've got 3 tables, products, products_tags and tags. A product can be connected to multiple tags via the products_tags table.

But if i would like to search on a product now with multiple tags, i do a query like this:

SELECT
    *
FROM
    products
LEFT JOIN
    products_tags
ON
    products_tags.product_id = products.id
LEFT JOIN
    tags
ON
    products_tags.tag_id = tags.id
WHERE
    tags.name = 'test'
AND
    tags.name = 'test2'

Which doesn't work :(. If i remove the AND tags.name = 'test2' it works. So i can only search by one tag, i explained the query and it said impossible where.

How can i search on multiple tags using a single query?

Thanks!

Upvotes: 1

Views: 1067

Answers (4)

Denis de Bernardy
Denis de Bernardy

Reputation: 78483

you need to join twice for test and test2:

select products.*
from products
join product_tags as product_tag1 on ...
join tags as tag1 on ...
join product_tags as product_tag2 on ...
join tags as tag2 on ...
where tag1.name = 'test'
and tag2.name = 'test2'

for test or test2, you need one join and an in clause and a distinct:

select distinct products.*
from products
join product_tags on ...
join tags as tags on ...
where tags.name IN('test', 'test2')

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65547

If you are searching for products that have BOTH the "test" and "test2" tags, then you will need to join to the product_tag and tag table twice each.

Also, use inner joins since you only want the products that have these tags.

Example:

SELECT products.*
FROM products
INNER JOIN products_tags pt1 ON pt1.product_id = products.id
INNER JOIN products_tags pt2 ON pt2.product_id = products.id
INNER JOIN tags t1 ON t1.id = pt1.tag_id
INNER JOIN tags t2 ON t2.id = pt2.tag_id
WHERE t1.name = 'test'
AND t2.name = 'test2'

Upvotes: 1

DRapp
DRapp

Reputation: 48139

You'll have to do a group by and COUNT(*) to ensure BOTH (or however many) are ALL found. The first query (PreQuery) joins the products tags table to tags and looks for same with matching count of tags to find... THEN uses that to join to products for finalized list

SELECT STRAIGHT_JOIN
      p.*
   FROM
      ( select pt.product_id
           from products_tags pt
                   join tags on pt.tag_id = tags.id
           where tags.name in ('test1', 'test2' )
           group by pt.product_id
           having count(*) = 2
      ) PreQuery
      join products on PreQuery.Product_ID = Products.ID

Upvotes: 1

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27496

Have you tried something like:

WHERE
    (tags.name = 'test'
OR
    tags.name = 'test2')

Or 

WHERE
    tags.name in( 'test', 'test2')

Because even if you join one product to multiple tags, each tag record only has one value for name.

Upvotes: 3

Related Questions