CODEWITHSUNDEEP
mysql
Bounce
Bounce

Reputation: 2105

Selecting records from one table

I have following table (tbl_product_rel):

id  product_id  attr_id
1      1           10
2      1           15
3      1           20
4      2           6
5      2           9
6      3           10
7      3           15
8      3           20
9      4           15

I want to get product_ids which have attr_ids exactly equal to 10,15,20. If I use MySQL IN

SELECT DISTINCT(product_id) FROM tbl_product_rel WHERE attr_id IN (10,15,20)

so I get also product_id which has attr_id equal to 15, but it does not have attr_ids 10 and 20.

Any ideas ?

Upvotes: 1

Views: 72

Answers (4)

mkk
mkk

Reputation: 7693

The reason why you might get impression that your query does not work is probably due to DISTINCT function.
Think what is your desired output? You want get all product_id where attr_id is 10, 15 or 20. It means you want following ids:

id  product_id  attr_id
1      1 OK          10 
2      1 OK          15
3      1 OK          20
4      2 NO          6
5      2 NO          9
6      3 OK          10
7      3 OK          15
8      3 OK          20
9      4 OK          15

so the basic query without distinct should return:

1, 1, 1, 3, 3, 3, 4

if you use distinct, you will get unique ids:

1, 3, 4

I believe this is what you get.

Chances are you want "id" instead of "product_id".

UPDATE:

Ahh I might now what is your problem. You expect to get product_id which satisfies all 3 conditions. You cannot use IN because IN joins ids with OR. The query that you have written could be rewritten as:

`SELECT DISTINCT(product_id) FROM tbl_product_rel WHERE attr_id = 10 OR attr_id = 15 OR attr_id = 20`.

If you are sure that there are no duplicates (ie. 2 records with attr_id = 20), your solution would be

`SELECT product_id FROM tbl_product_rel WHERE attr_id IN (10, 15, 20) GROUP BY product_id HAVING count(*) = 3

but this will not work if you allow duplicates, for example in this scenario:

id  product_id  attr_id
1      1          10 
2      1          20
3      1          20

you will get 1 but you shouldn't (according to what you need). Plase let me know if you allow such scenarios.

Upvotes: 2

Ilion
Ilion

Reputation: 6882

It sounds like you want only product_ids with all three attribute ids. You can do it with some ugly subqueries:

SELECT DISTINCT(product_id) FROM tbl_product_rel
  WHERE product_id IN 
    (SELECT product_id FROM tbl_product_rel WHERE attr_id=10)
  AND product_id IN
    (SELECT product_id FROM tbl_product_rel WHERE attr_id=15)
  AND product_id IN
    (SELECT product_id FROM tbl_product_rel WHERE attr_id=20)

You could also do some uglier joins.

Upvotes: 1

boobiq
boobiq

Reputation: 3024

You can use query like this, but i don't know if this is the best solution :)

SELECT * FROM tbl_product_rel WHERE attr_id IN (10,15,20) GROUP BY product_id HAVING COUNT(product_id) = 3

Upvotes: 4

Harshith J.V.
Harshith J.V.

Reputation: 897

Remove distinct function:

SELECT product_id FROM tbl_product_rel WHERE attr_id IN (10,15,20)

Upvotes: 0

Related Questions