1

I have following tables

Tags

id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1

products

id | proudct_name
1|product1
2|product2

product_tags

id | product_id | tag_id 
1|1||1
2|1|2
3|2|1

i need retrieve only those product which belongs to both tag1 and tag2

  select * from `products` INNER JOIN product_tags ON products.id=product_tags.product_id

INNER JOIN tags ON tags.id=product_tags.tag_id WHERE product_tags.tag_id=1 AND product_tags.tag_id=2

But my query return empty result

js fiddle

http://sqlfiddle.com/#!9/34f43a/1/0

5
  • Are you sure your first table's data is correct? Also, could you please post DDL (tables) and DML (data) for your tables or a fiddle? p.s. welcome to the forum! :=)
    – Vérace
    Jan 11, 2020 at 5:47
  • @Vérace.yes first table data is correct
    – scott
    Jan 11, 2020 at 5:48
  • And the table structures? The data as CREATE TABLE foo (...);?
    – Vérace
    Jan 11, 2020 at 6:03
  • @Vérace. here is the link sqlfiddle.com/#!9/34f43a/1/0
    – scott
    Jan 11, 2020 at 6:03
  • @Vérace.updated table structure
    – scott
    Jan 11, 2020 at 6:04

1 Answer 1

1

If I've understood you correctly, you want something like this (see fiddle here):

SELECT 
  p.id AS p_pid, p.product_name AS p_name, 
  p.product_category_id AS p_cat_id,
  pt.product_id AS pt_pid, pt.tag_id AS pt_tag_id, -- other product_tag fields...
  t.tag_name AS t_name, 
  t.slug AS t_slug_name                            -- other tag fields...
FROM products p
INNER JOIN product_tags pt 
  ON p.id = pt.product_id
INNER JOIN tags t
  ON t.id = pt.tag_id 
WHERE pt.tag_id = 1 OR pt.tag_id = 6
ORDER BY product_id, tag_id;

Result:

p_pid     p_name p_cat_id pt_pid pt_tag_id        t_name    t_slug_name
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         6            Mi              mi
    1   product1        4      1         6            Mi              mi
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         6            Mi              mi
    2   product2        4      2         6            Mi              mi

Notice in the fiddle how I've arranged the SQL. I've used aliases and meaningful names for my fields, prefixing them with the table initials. You mightn't want to do that for the final query, but it sure helps when constructing your query and you have different, say, product_ids coming in from different tables through the JOINs. The aliases also help with legibility along with liberal use of spaces.

I think because of this lack of clarity, you missed this piece of code:

WHERE product_tags.tag_id=1 AND product_tags.tag_id=6

which, when turned into this, becomes much more legible:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

Now, it's glaringly obvious where the error is - you can't have a tag_id = 1 AND = 6 at the same time, hence your empty result set from your original query. So, just changing that line to:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

gives the result which is I believe what you were looking for. If not, let me know and I will endeavour to correct any problems/misunderstandings!

Oh, yes, a couple of final words. Please always include your version of MySQL - it's particularly important with this RDBMS as they've introduced many important features recently! Also, see the importance of providing good sample data - good questions get good answers here and yours has now become a good one with the addition of the thorough fiddle (+1)!

1
  • @Thank you for your effort,
    – scott
    Jan 11, 2020 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.