Select result that match ALL array values

Question

I'm trying to solve this for quite a moment now and I don't seem to be able to do it by myself.

I'd like to store OPTIONS linked to IDs, and when needed, get the results that match all wanted OPTIONS. I thought about doing it this way:

ID    |    OPTION

aaa   |  1
aaa   |  2
aaa   |  3

bbb   |  1
bbb   |  2

ccc   |  1
ccc   |  2
ccc   |  5
ccc   |  7

Where ID and OPTION are FOREIGN KEYS.

The final request would look like

options_wanted(1,2,5,7)
SELECT * FROM main_table 
WHERE crit1=... 
AND crit2=... 
AND (ALL OPTIONS ARE FOUND IN options TABLE)

Can I make it work or should I change the implementation?

What do you suggest me?

EDIT:
Thanks to https://stackoverflow.com/a/7505147/2512108, I almost found what I want.

His query works but the last column only gives the 1st option alone. Is there a way to make it return ALL the options AVAILABLE (not only the wanted ones) ?

If the goal you want to achieve is to get the list of options from table then you can use `select distinct option from table_name` and then may be add a condition for the specific id using `where id="your_desired_id"` — maksbd19, Apr 03 '15 at 12:00
maksbd19 : I also need to restrain results to the EXACT set of values Karoly Horvath : crit1 is stored in main_table — JeanneD4RK, Apr 03 '15 at 12:05
You can use GROUP_CONCAT() to transform all options per id into an ordered string which you can then compare to to your desired option string — pala_, Apr 03 '15 at 12:10
@pala_: I don't see how you could do the comparison... e.g.: how do you find `1,5` in `1,2,5,7`? — Karoly Horvath, Apr 03 '15 at 12:13
It is my understanding he doesn't want that. It has to have all of the options and no additional options. — pala_, Apr 03 '15 at 12:16
@pala_: "and no additional options"? ask the OP, he never said that. — Karoly Horvath, Apr 03 '15 at 12:18
@JeanneD4RK Buddy; Try with my Edited logic and tell me whether it works — Tushar, Apr 03 '15 at 12:19
Okay so it has to have all options required. Easy. You limit with where to options that are IN the required options, then do the group concat. It does say ALL options are in the options table — pala_, Apr 03 '15 at 12:21
@JeanneD4RK is my above assumption correct? Can you please check my answer below and let me know if the results are incorrect? — pala_, Apr 03 '15 at 13:28

pala_ · Answer 1 · 2015-04-03T13:41:54.790

Answer:

select item_id, group_concat(option_id order by option_id asc) options
  from options
  where option_id in (1, 2, 3)
  group by item_id
  having count(option_id) = 3

Fiddle: http://sqlfiddle.com/#!9/04f69/3

I'll leave the joining to your other table up to you, as well as the other criteria since the table schema isn't really explicitly mentioned.

EDIT No I won't, I hate half an answer.

select item_id, group_concat(option_id order by option_id asc) options
  from main_table m
  inner join options o
    on m.id = o.item_id
  where option_id in (1, 2, 3)
    AND crit1 = 2
    AND crit2 = 3
  group by item_id
  having count(option_id) = 3

Updated fiddle: http://sqlfiddle.com/#!9/45bee/1

And if you want it to return ALL options available to an item that has at minimum all of the REQUIRED options, your query is this:

 select o.item_id, group_concat(o.option_id) options
   from options o
   inner join (
     select item_id
       from main_table m
       inner join options o
         on m.id = o.item_id
       where option_id in (1, 2, 3)
       AND crit1 = 2
       AND crit2 = 3
     group by item_id
     having count(option_id) = 3

With a final fiddle here: http://sqlfiddle.com/#!9/d60b3/1

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

As you state: 'Criterias and options are different'

and

'All options are found in options table'

I assume you have two tables; Namely main_table and options_table

SELECT id, option 
FROM main_table
WHERE crit1='value1'
and crit2 = 'value2'
and id IN(
        SELECT id
          FROM main_table
          WHERE option IN (select options from options_table)
          GROUP BY id
          HAVING COUNT(*) = (select count(options) from options_table)

Find similar question over here: SQL selecting rows where one column's value is common across another criteria column

edited May 23 '17 at 11:50

Community

1
1

answered Apr 03 '15 at 11:57

Tushar

3,527
9
27
49

Criterias and options are different. Plus, IN will return the result if ONE match is found, I need them all... – JeanneD4RK Apr 03 '15 at 12:01
You want all options? (1,2,5,7) Right? Let me modify my query – Tushar Apr 03 '15 at 12:02
"Criterias and options are different." – Karoly Horvath Apr 03 '15 at 12:20
1

Your last solution works (as I stated in my original post edit) but I would need it to return all options it has if possible. If not, I will make a 2nd query to get them with the returned ID... – JeanneD4RK Apr 03 '15 at 12:50
Thanks :) Happy to know that :) Am keeping only last query in answer. You can list all options to compare.. Right...? And you will be knowing it's count() too? – Tushar Apr 03 '15 at 12:53
I edited my previous answer. Hope it shall work now as per your edit. – Tushar Apr 03 '15 at 13:18

score 0 · Answer 3 · answered Apr 03 '15 at 13:46

0

MADE IT ! Finally, thanks to you

SELECT main_table.*, (SELECT GROUP_CONCAT(feature_id) FROM featuring WHERE main_id = main_table.id) options FROM main_table
RIGHT JOIN featuring 
    ON main_table.id = featuring.main_id 
WHERE featuring.feature_id IN (WANTEDOPTIONS) 
GROUP BY main_table.id 
HAVING COUNT(DISTINCT featuring.feature_id) = NUMBEROFWANTEDOPTIONS

It gives me all the informations on the main and ALL the AVAILABLE options in featuring.
Thanks again.

answered Apr 03 '15 at 13:46

JeanneD4RK

323
1
5
19

1

i'm glad you got an answer, but if this is based on mine either an upvote or an accepted answer would be appreciated. – pala_ Apr 03 '15 at 14:02

Select result that match ALL array values

3 Answers3

Linked