CODEWITHSUNDEEP
mysqlsql
Jspr
Jspr

Reputation: 77

How to find the exact group that matches to defined values?

I simplified the table so it is easier to understand.

I have a table with groups and a group exists of multiple values. Here is the table:

VALUE | GROUP
  A   |   1
  B   |   1
  A   |   2
  C   |   2
  B   |   3
  A   |   4
  B   |   4
  A   |   5
  B   |   5
  C   |   5

I want to give values to my query wich I programmatically build and find the exact group that matches to these values.

For example if I give value A and B to my query I want as a result group 1 and 4

A  ---------------> null 
A and B ----------> 1 and 4 
A , B and C ------> 5 
B  ---------------> 3 
A and C ----------> 2 
C ----------------> null

Upvotes: 3

Views: 254

Answers (1)

mellamokb
mellamokb

Reputation: 56769

You can use a query like the following (assuming value,group pairs unique):

select `GROUP`
from MyTable
group by `GROUP`
having count(`VALUE`) = count(case when `VALUE` IN ('a','b') then 1 end)
   and count(case when `VALUE` IN ('a','b') then 1 end) = @Count;

Where ('a','b') would be the list of values you are testing for, and @Count would be the count of different values in your check set (2 in this case).

Demo: http://www.sqlfiddle.com/#!2/78def/13

Upvotes: 3

Related Questions