Find rows from the results of one query that meet another requirement

Question

I have the following table, product_taxonomy_id:

| product_id | taxonomy_id | value_id |
+------------+-------------+----------+
|     1      |      2      |     4    |
|     1      |      1      |     1    |
|     2      |      1      |     1    |
|     2      |      3      |     8    |
|     3      |      2      |     5    |
|     3      |      1      |     2    |
|     4      |      1      |     1    |
|     4      |      2      |     4    |
|     4      |      3      |     8    |

I want to get product_ids that have taxonomy = 1 and value_id = 1 AND taxonomy = 2 and value_id = 4.

The products that meet the first requirement are: 1, 2, 4.

From those, the requirements that meet the second requirement are: 1, 4

I am able to do it with the following query:

SELECT DISTINCT `product_id` FROM product_taxonomy_value
INNER JOIN (SELECT DISTINCT `product_id` FROM product_taxonomy_value WHERE `taxonomy_id` = 1 AND `value_id` = 1) a USING (`product_id`)
INNER JOIN (SELECT DISTINCT `product_id` FROM product_taxonomy_value WHERE `taxonomy_id` = 2 AND `value_id` = 4) b USING (`product_id`)

Is there a better solution?

Oleg Gumennyj · Answer 1 · 2014-12-19T17:37:44.190

1

Yes, you can use this query:

SELECT DISTINCT product_id 
  FROM product_taxonomy_value
    WHERE taxonomy_id IN (1, 2)
      AND value_id IN (1, 4);

EDIT: If tronmcp right about the subject matter, then you can use this query:

SELECT DISTINCT product_id 
  FROM product_taxonomy_value
    WHERE product_id IN (SELECT product_id FROM product_taxonomy_value WHERE taxonomy_id = 1 AND value_id = 1)
      AND taxonomy_id = 2
      AND value_id = 4;

edited Dec 19 '14 at 17:37

answered Dec 19 '14 at 17:11

Oleg Gumennyj

190
1
2
10

1

The problem with this query is if you had a forth product with taxonomy_id = 1 and value_id = 4 it would also show up on the result and it shouldn't since the combinations should be: the products that have taxonomy = 1 and value_id = 1 are : 1, 2, 4 and now for this products, the products that have taxonomy = 2 and value_id = 4 are (1,4), so the combination of 1 and 4 respectively is not valid. – tronmcp Dec 19 '14 at 17:17
Good edit, I would like to add that @tronmcp is right, and your second query does give the same results as OP's original query. Here is a [Fiddle](http://sqlfiddle.com/#!2/3f35a4/6). – AdamMc331 Dec 19 '14 at 19:30

score 1 · Accepted Answer · answered Dec 19 '14 at 19:19

I think you should use subqueries for this. First, break this up into pieces and put them back together.

You want to get product_ids that have a taxonomy_id of 1 and a value_id of 1. You can do this with a simple WHERE clause:

SELECT DISTINCT product_id
FROM product_taxonomy_value
WHERE taxonomy_id = 1 AND value_id = 1;

Now, you want to get the results from that, that meet the other requirements. What I would do is write a second query that gets the ids that meet the second requirement:

SELECT DISTINCT product_id
FROM product_taxonomy_value
WHERE taxonomy_id = 2 AND value_id = 4;

And you can add a subquery requirement that ensures the ids returned from the second query are also returned by the first, like this:

SELECT DISTINCT product_id
FROM product_taxonomy_value
WHERE taxonomy_id = 2 AND value_id = 4 AND product_id IN(
  SELECT DISTINCT product_id
  FROM product_taxonomy_value
  WHERE taxonomy_id = 1 AND taxonomy_id = 1);

I ran this in SQL Fiddle, and it was much faster than your first one, but both returned the same results.

Thanks @McAdam331 for editing the title and removing the confusion from the question. — tronmcp, Dec 21 '14 at 00:38
@Josep glad to help. If you used any of the answers to solve your problem, please upvote and accept it by clicking the up arrow and checkmark next to the answer that helped you. — AdamMc331, Dec 22 '14 at 13:49

score 0 · Answer 3 · answered Dec 19 '14 at 17:16

0

If I understood your question correctly following should give your correct answer

SELECT DISTINCT product_id
FROM product_taxonomy_value
WHERE 
    (taxonomy_id = 1 AND value_id = 1) 
    OR (taxonomy_id = 2 AND value_id = 4);

answered Dec 19 '14 at 17:16

Gihan

4,163
6
31
49

This won't work, because it will also return product_id 2 but product_id 2 doesn't meet both requirements. – AdamMc331 Dec 19 '14 at 19:29
correct, this won't work but thank you for your answer – Josep Dec 22 '14 at 07:12

Find rows from the results of one query that meet another requirement

3 Answers3