Equivalent of "IN" that uses AND instead of OR logic?

Question

I know I'm breaking some rules here with dynamic SQL but I still need to ask. I've inherited a table that contains a series of tags for each ticket that I need to pull records from.

Simple example... I have an array that contains "'Apples','Oranges','Grapes'" and I am trying to retrieve all records that contain ALL items contained within the array.

My SQL looks like this:

 SELECT * FROM table WHERE basket IN ( " + fruitArray + " )

Which of course would be the equivalent of:

 SELECT * FROM table WHERE basket = 'Apples' OR basket = 'Oranges' OR basket = 'Grapes'

I'm curious if there is a function that works the same as IN ( array ) except that it uses AND instead of OR so that I can obtain the same results as:

 SELECT * FROM table WHERE basket LIKE '%Apples%' AND basket LIKE '%Oranges%' AND basket LIKE '%Grapes%'

I could probably just generate the entire string manually, but would like a more elegant solution if at all possible. Any help would be appreciated.

Answer 1

This is a very common problem in SQL. There are basically two solutions:

1. Match all rows in your list, group by a column that has a common value on all those rows, and make sure the count of distinct values in the group is the number of elements in your array.

   SELECT basket_id FROM baskets
   WHERE basket IN ('Apples','Oranges','Grapes')
   GROUP BY basket_id
   HAVING COUNT(DISTINCT basket) = 3
   

2. Do a self-join for each distinct value in your array; only then you can compare values from multiple rows in one WHERE expression.

   SELECT b1.basket_id
   FROM baskets b1
   INNER JOIN baskets b2 USING (basket_id)
   INNER JOIN baskets b3 USING (basket_id)
   WHERE (b1.basket, b2.basket, b3.basket) = ('Apples','Oranges','Grapes')
   

Answer 2

There may be something like that in full text search, but in general, I sincerely doubt such an operator would be very useful, outside the conjunction with LIKE.

Consider:

SELECT * FROM table WHERE basket ='Apples' AND basket = 'Oranges'

it would always match zero rows.

Answer 3

If basket is a string, like your example suggests, then the closest you could get would be to use LIKE '%apples%oranges%grapes%', which could be built easily with '%'.implode('%', $tags).'%'

The issue with this is if some of 'tags' might be contained in other tags, e.g. 'supercalifragilisticexpialidocious' LIKE '%super%' will be true.

Answer 4

If you need to do LIKE comparisons, I think you're out of luck. If you are doing exact comparisons invovling matching sets in arbitrary order, you should look into the INTERSECT and EXCEPT options of the SELECT statement. They're a bit confusing, but can be quite powerful. (You'd have to parse your delimited strings into tabular format, but of course you're doing that anyway, aren't you?)

Answer 5

Are the items you're searching for always in the same order within the basket? If yes, a single LIKE should suffice:

SELECT * FROM table WHERE basket LIKE '%Apples%Oranges%Grapes%';

And concatenating your array into a string with % separators should be trivial.