CODEWITHSUNDEEP
mysqlsqljsp
ømi
ømi

Reputation: 521

Issue in SQL statement to get value in combination of 3 values

Prod_Oth_Id ||| Prod_Code ||||| Prod_Details |||||||||| Prod_Value |

    1     | BR25MAON  | 4            | 9          |
    2     | BR25MAON  | 5            | 10         |
    3     | BR25MAON  | 6            | 11         |
    4     | BR25MABO  | 4            | 9          |
     5    | BR25MABO  | 5            | 10         |
     6    | BR25MABO  | 6            | 17         |
     7    | BR25GLON  | 4            | 9          |
        8 | BR25GLON  | 5            | 16         |
        9 | BR25GLON  | 6            | 11         |
       10 | BR25GLBO  | 4            | 9          |
       11 | BR25GLBO  | 5            | 16         |
       12 | BR25GLBO  | 6            | 17         |

I have combination of 3 prod_value for eg 9,10,11 & I want to retrieve prodcode for for above combination which is BR25MAON

for eg if I got 9, 16, 17 then I must retrieve prodcode for this combination which is BR25GLBO

Upvotes: 2

Views: 87

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

You can count the matches and non matches, if there are 3 Prod_Values to match, there should be 3 matches and 0 non matches;

# 9, 10, 11

SELECT DISTINCT a.Prod_Code FROM MyTable a GROUP BY Prod_Code
HAVING SUM(a.Prod_Value NOT IN (9,10,11)) = 0               # <-- No non matches
   AND SUM(a.Prod_Value     IN (9,10,11)) = 3;              # <-- 3 matches

If you for example were matching (1,2), the query would change to;

# 1, 2

SELECT DISTINCT a.Prod_Code FROM MyTable a GROUP BY Prod_Code
HAVING SUM(a.Prod_Value NOT IN (1,2)) = 0                   # <-- No non matches
   AND SUM(a.Prod_Value     IN (1,2)) = 2;                  # <-- 2 matches

SQLfiddle demo

Upvotes: 2

Ali Adravi
Ali Adravi

Reputation: 22803

If you want to get the product code for different product value then simply use this:

Select Distinct Prod_Code From TableName Where Prod_Value IN (9,10,11)

and it will return only BR25GLBO

If you need something else please explain your requirement in detail.

Upvotes: 0

Related Questions