mysql query to run query after grouping rows based on product_id

Question

I want to write a mysql query to fetch distinct product_id where combination of attribute_id column for the similar product_id values to be displayed.

I tried this one:

SELECT DISTINCT(product_id) FROM oc_product_attribute WHERE (attribute_id = 26 AND CAST(text AS UNSIGNED) >= '1') and (attribute_id = 29 AND text = 'Flange')

   product_id  attribute_id    language_id     text
    1           12              1               0.25 - 20
    1           13              1               2500
    1           14              1               30
    1           15              1               130-160
    1           16              1               1.00
    1           17              1               0.50
    1           18              1               Medium accuracy
    1           19              1               PC mountable
    1           20              1               N/A
    2           21              1               50
    2           22              1               +/- +/-100
    2           23              1               +5
    2           24              1               2.5 v to +/-0.625
    2           25              1               DC to 20
    2           26              1               1.00
    2           27              1               < 1 % of FS
    2           28              1               4 PIN Connector
    2           29              1               Flange
    2           30              1               N/A

So basically what i want is, a query should be run on different rows of similar product_id column where logical operator with concatenation and logical operators.

Please let me know, if the question is not clear.

Basha, I'm afraid the question isn't quite clear. Could you add a few sample rows and expected results? — Ed Gibbs, Dec 23 '14 at 14:49
not possible. you're requiring that `attribute_id` has two different values AT THE SAME TIME. `a_id = 26 AND a_id 29`. Perhaps you want `OR` instead? `(a_id = 26 and cast(...)) OR (a_id = 29 AND text = flange))`? — Marc B, Dec 23 '14 at 14:49
@EdGibbs, i have given snapshot of my table please look in to that — Basha Shaik, Dec 23 '14 at 14:52
@marc b, i tried or condition also, but the it is not the expected result. as it is a search page request. all the conditions has to be met, which would be AND only. So for this requirement what should i do.let me know plz. — Basha Shaik, Dec 23 '14 at 14:54
Considering your sample table, what is the desired result of your query? — Tab Alleman, Dec 23 '14 at 14:55
@TabAlleman, desired result for that query should be only 2, which is product_id — Basha Shaik, Dec 23 '14 at 14:56
@basha: explain to me how a variable can have two different values at the same time. Because that's what your all-and version is requiring right now. — Marc B, Dec 23 '14 at 14:59
@Marc B, he's got a normalized table and he needs to find Product_ids that have Attribute26 >= 1 and Attribute29='Flange' — Tab Alleman, Dec 23 '14 at 15:04
@TabAlleman: exactly. but as written, the query is requiring that one SINGLE record/row has two different values in that ONE field. — Marc B, Dec 23 '14 at 15:06
Oh yes, the query is the problem. It wasn't until he gave his desired results that I understood what he was trying to do. See my answer below. — Tab Alleman, Dec 23 '14 at 15:08
Note that DISTINCT is not a function. Also, the question is unclear. — Strawberry, Dec 23 '14 at 15:39
possible duplicate of [Select values that meet different conditions on different rows?](http://stackoverflow.com/questions/477006/select-values-that-meet-different-conditions-on-different-rows) — AdamMc331, Dec 24 '14 at 04:44

Tab Alleman · Accepted Answer · 2014-12-23T18:42:52.940

0

You need to do EXISTS()...

SELECT DISTINCT product_id
FROM oc_product_attribute t1 
WHERE EXISTS(SELECT * FROM oc_product_attribute t2 
  WHERE t1.product_id=t2.product_id
  AND attribute_id = 26 AND CAST(text AS UNSIGNED) >= '1'
) 
AND EXISTS (SELECT * FROM oc_product_attribute t3
  WHERE t1.product_id=t3.product_id
AND attribute_id = 29 AND text = 'Flange')

If you need to test for more attribute/text combinations, just add another EXISTS() clause for each one.

edited Dec 23 '14 at 18:42

answered Dec 23 '14 at 15:01

Tab Alleman

31,483
7
36
52

OP is short for Original Poster. So in this case, that's you Basha. – Tab Alleman Dec 23 '14 at 18:40

AdamMc331 · Answer 2 · 2014-12-23T15:21:30.433

You can use a subquery along with the IN operator to solve this. Think of it this way.

First, write a query that pulls all product_ids that match the first requirement:

SELECT DISTINCT product_id
FROM oc_product_attribute
WHERE (attribute_id = 26 AND CAST(text AS UNSIGNED) >= '1');

Then, write a second query that pulls all product_ids that match the first requirement:

SELECT DISTINCT product_id
FROM oc_product_attribute
WHERE (attribute_id = 29 AND text = 'Flange');

Now, you can write your final query with the idea that you want to select all rows from the first result set whose product_id value is also in the results of the second set. Try this:

SELECT DISTINCT product_id
FROM oc_product_attribute
WHERE (attribute_id = 26 AND CAST(text AS UNSIGNED) >= '1') AND product_id IN(
   SELECT DISTINCT product_id
   FROM oc_product_attribute
   WHERE (attribute_id = 29 AND text = 'Flange'));

@Strawberry thank you for catching that. I know better than that, but I made the lazy mistake of copying OP's query and adjusting it, and never made a Fiddle that blew up in my face. Thanks a bunch. — AdamMc331, Dec 23 '14 at 15:22
Well the fiddle would work. MySQL overlooks the error, but I think to treat it as though it were a function perpetuates a misunderstanding of its role in relation to SELECT. — Strawberry, Dec 23 '14 at 15:23

score -2 · Answer 3 · answered Dec 23 '14 at 15:04

-2

You can try this:

select distinct p_id from (SELECT DISTINCT(product_id) as p_id FROM oc_product_attribute WHERE (attribute_id = 26 AND CAST(text AS UNSIGNED) >= '1') or (attribute_id = 29 AND text = 'Flange'))

answered Dec 23 '14 at 15:04

Prerak Sola

9,517
7
36
67

And DISTINCT is still not a function – Strawberry Dec 23 '14 at 15:17
@Strawberry: Please read this, http://msdn.microsoft.com/en-us/library/ee634943.aspx – Prerak Sola Dec 23 '14 at 15:22
1

This is a question about MySQL. Why would I follow that link? – Strawberry Dec 23 '14 at 15:24
because it works the same in MySQL too. I tried it. You can try it too yourself. – Prerak Sola Dec 23 '14 at 15:27
I'm simply stating a fact. I've not made any comment about what may or may not work! Incidentally, your query would fail - and is uncessarily convoluted - but for a different reason than I've described above. You can try it too yourself ;-) – Strawberry Dec 23 '14 at 15:28

mysql query to run query after grouping rows based on product_id

3 Answers3