CODEWITHSUNDEEP
mysqlsql
alioygur
alioygur

Reputation: 5464

mysql select problem

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

Upvotes: 1

Views: 310

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

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

Upvotes: 0

Amadan
Amadan

Reputation: 198314

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'

Upvotes: 0

milanseitler
milanseitler

Reputation: 765

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..

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838006

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.

Upvotes: 3

Related Questions