2

I have a table like this:

Col1    Col2
word1   872
word1   333
word2   631
word3   982
word3   111
word4   111
word5   631
word6   333
word6   453

I would like to receive the results of multiple SELECTs, but only those results which occur in all results. For example, if I am searching for SELECT col2 FROM table WHERE col1='word1' and SELECT col2 FROM table WHERE col1='word6', I want to join these two statements together to have a single MySQL statement which will return only the results that occur in both of the above example queries. (The same as array_intersect works in PHP.)

I need a single query that will do this for any number of col1 words.

Sorry, if I haven't explained very well. It was difficult to explain.

Alasdair
  • 13,348
  • 18
  • 82
  • 138
  • See http://stackoverflow.com/q/2621382/549755 to see how to implement "INTERSECTS" in MySQL. – Odi Feb 11 '13 at 13:55

1 Answers1

1

The best approach to this problem is to use aggregation:

select col2
from t
group by col2
having max(col1 = 'word1') = 1 and
       max(col1 = 'word2') = 1

This easily generalizes to more words.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786