mysql select problem

Question

I have like follow table.

And I want to select match this-> "Tip-1 = 225 and Tip-2 = 65 and Tip3 = 16" product.

There are 1 products that match the query. The product ID 2.

But I do not know how to do please help me.

id  product_id  option_id   name    label   value
44  1                   4   Tip-5   Tip 5   Dört Mevsim
42  1                   2   Tip-2   Tip 2   65
48  2                   4   Tip-5   Tip 5   Dört Mevsim
47  2                   3   Tip-3   Tip 3   16
46  2                   2   Tip-2   Tip 2   65
45  2                   1   Tip-1   Tip 1   225
52  3                   4   Tip-5   Tip 5   Dört Mevsim
51  3                   3   Tip-3   Tip 3   16
50  3                   2   Tip-2   Tip 2   75
49  3                   1   Tip-1   Tip 1   215
60  4                   4   Tip-5   Tip 5   Dört Mevsim
58  4                   2   Tip-2   Tip 2   75
64  5                   4   Tip-5   Tip 5   Dört Mevsim
63  5                   3   Tip-3   Tip 3   12
62  5                   2   Tip-2   Tip 2   85
61  5                   1   Tip-1   Tip 1   155
59  4                   3   Tip-3   Tip 3   16
43  1                   3   Tip-3   Tip 3   16
41  1                   1   Tip-1   Tip 1   205
57  4                   1   Tip-1   Tip 1   205
72  6                   4   Tip-5   Tip 5   Dört Mev

Mark Byers · Accepted Answer · 2011-03-17 11:37:18Z

3

Try this:

SELECT product_id
FROM yourtable
WHERE (name, value) IN (('Tip-1', '225'), ('Tip-2', '65'), ('Tip-3', '16'))
GROUP BY product_id
HAVING COUNT(*) = 3

This assumes that (product_id, name) is unique. If not, use COUNT(DISTINCT name) instead.

answered Mar 17, 2011 at 11:37

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RichardTheKiwi Over a year ago

This will perform very badly and not make use of any indexes. tuples = pretty, performance = bad

milanseitler · Accepted Answer · 2011-03-17 11:39:17Z

0

It would be better to have own table for product details but to do your select try this

SELECT * FROM table_name WHERE (name = "Tip-1" AND value = 225) AND (name = "Tip-2" AND value = 65) AND (name = "Tip-3" AND value = 16) GROUP BY product_id

This might work but the table structure is really terrible, you should chane it if it's possible..

answered Mar 17, 2011 at 11:39

milanseitler

7751 gold badge7 silver badges22 bronze badges

2 Comments

Amadan Over a year ago

This can't work - you're looking for a row where name is both Tip-1 and Tip-2 at the same time

milanseitler Over a year ago

I thought that GROUP BY statement could solve this. My bad, sorry.

Amadan · Accepted Answer · 2011-03-17 11:39:57Z

0

First of all, unless you have a very specific need, this is a horrible database design. That said,

SELECT T1.product_id
FROM mytable T1
JOIN mytable T2 ON T1.product_id = T2.product_id
JOIN mytable T3 ON T1.product_id = T3.product_id
WHERE T1.name = 'Tip-1' AND T1.value = '255'
  AND T2.name = 'Tip-2' AND T2.value = '65'
  AND T3.name = 'Tip-3' AND T3.value = '16'

answered Mar 17, 2011 at 11:39

Amadan

200k23 gold badges253 silver badges321 bronze badges

1 Comment

alioygur Over a year ago

you are right. but No other remedy. i want adding to additional options product category. (ex: i sell t shirt tshirt options is size or color and i sell car car options gear type, color brand ). for it had to use. If you have a better idea, please tell your. sorry im little speak engilish

RichardTheKiwi · Accepted Answer · 2011-03-17 11:56:54Z

0

A regular group by with OR clauses and HAVING should do it

SELECT   product_id
FROM     yourtable
WHERE    (name = 'Tip-1' and value = '225')
   OR    (name = 'Tip-2' and value = '65')
   OR    (name = 'Tip-3' and value = '16')
GROUP BY product_id
HAVING   COUNT(distinct value) = 3

answered Mar 17, 2011 at 11:56

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Collectives™ on Stack Overflow

mysql select problem

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related