PostgreSQL multiple tuples selection in WHERE clause

Question

I have this table:

create table myTable (keyword text, category text, result text
                    , primary key (keyword,category));

insert into myTable values
    ('foo',       'A', '10'),
    ('bar',       'A', '200'),
    ('baz',       'A', '10'),
    ('Superman',  'B', '200'),
    ('Yoda',      'B', '10'),
    ('foo',       'C', '10');

I want to retrieve results according to tuples (keyword,category). So basically, with one easy tuple I have the following query:

SELECT result FROM myTable WHERE keyword LIKE '%a%' AND category = 'A';
-- returns 10,200 as expected

But I can have as many tuples as I want. Extending this query for several tuples returns bad results:

SELECT result FROM myTable
    WHERE ( keyword LIKE '%a%' AND category = 'A')
    AND   ( keyword LIKE '%Superman%' AND category = 'B');
-- expected 200; but returned no rows...

SELECT distinct result FROM myTable
    WHERE ( keyword LIKE '%a%' AND category = 'A')
    OR   ( NOT(keyword LIKE '%Superman%') AND category = 'B');
-- expected 10; but returned 10,200...

That's pretty logical because PostgreSQL does not follow the operator order and parenthesis.

Only OR clauses are working. If I had only OR clauses, I'd use something like this:

SELECT result FROM myTable
    INNER JOIN (VALUES
      ('foo','C'),
      ('Superman', 'B')
    ) t(keyword,category) USING (keyword,category); -- 10,200 as expected

But it works only for OR and for strict equality. In my case I want to use a LIKE equality and I want to use AND, OR, AND NOT and OR NOT between the different tuples.

More precisely, when I write:

SELECT result FROM myTable
    WHERE ( keyword LIKE '%a%' AND category = 'A')
    AND   ( keyword LIKE '%Superman%' AND category = 'B');
 -- expected 200; but returned no row

I mean I want the INTERSECTION of results obtained by the two clauses. The first tuple return 10,200 and the second one 200. I want to return only 200 in this case.

Using an OR as suggesting in comments like this:

SELECT distinct result FROM myTable
    WHERE ( keyword LIKE '%a%' AND category = 'A')
    OR   ( keyword LIKE '%Superman%' AND category = 'B');

returns 10,200, but that's not that I want ...

Error in the code perhaps? Maybe you meant: `SELECT result FROM myTable WHERE ( keyword LIKE '%a%' AND category = 'A') OR ( keyword LIKE '%Superman%' AND category = 'B');`. If you have category A **AND** category B the result is obviously nothing. — Simo Kivistö, Mar 31 '15 at 09:23
As for `keyword LIKE '%a%' AND category = 'A'` => returns bar and baz as expected where as `NOT(keyword LIKE '%Superman%') AND category = 'B'` => returns yoda as expected — Simo Kivistö, Mar 31 '15 at 09:27
@SimoKivistö For the first query in error, I don't want an OR as I don't want to obtain every result for first tuples PLUS every result for second one. In fact that's an INTERSECTION that I need. — pidupuis, Mar 31 '15 at 09:28
@pidupuis for _expected 200; but returned no rows_ > try [this](http://pastie.org/10064451#1) — Vivek S., Mar 31 '15 at 10:27

score 2 · Accepted Answer · edited May 23 '17 at 11:43

What you seem to be looking for is called relational division. The task could be phrased as:

Find results that have at least one row matching these conditions:
keyword LIKE '%a%' AND category = 'A'
and at least one row matching these other conditions:
keyword LIKE '%Superman%' AND category = 'B'

A fast solution for conditions returning DISTINCT results:

SELECT DISTINCT result
FROM   tbl t1
JOIN   tbl t2 USING (result)
WHERE  t1.keyword LIKE '%a%' AND t1.category = 'A'
AND    t2.keyword LIKE '%Superman%' AND t2.category = 'B';

But since your filters can return multiple rows for each result, one of these will be faster:

SELECT result
FROM  (
     SELECT DISTINCT result
     FROM   tbl
     WHERE  keyword LIKE '%a%' AND category = 'A'
     ) t1
JOIN  (
     SELECT DISTINCT result
     FROM   tbl
     WHERE  keyword LIKE '%Superman%' AND category = 'B'
     ) t2 USING (result);

Or:

SELECT result
FROM  (
     SELECT DISTINCT result
     FROM   tbl
     WHERE  keyword LIKE '%a%' AND category = 'A'
     ) t
WHERE  EXISTS (
     SELECT 1
     FROM   tbl
     WHERE  result = t.result
     AND    keyword LIKE '%Superman%' AND category = 'B'
     );

SQL Fiddle.

We have assembled an arsenal of query techniques under this related question:

How to filter SQL results in a has-many-through relation

The solution using `EXISTS` seems better because it allows me to use `NOT`. How do you suggest I use it with more tuples alterning `AND` and `OR` ? (Can I combine several `EXISTS` and `UNION` without an infinite depth of embedded select ?) — pidupuis, Mar 31 '15 at 12:37
@pidupuis: You already know how to implement `OR`, add `EXISTS` / `NOT EXISTS` to the WHERE clause for `AND` / `AND NOT` ... — Erwin Brandstetter, Mar 31 '15 at 17:31

score 0 · Answer 2 · answered Mar 31 '15 at 09:26

You were almost there:

SELECT distinct result FROM myTable
WHERE ( keyword LIKE '%a%' AND category = 'A')
OR   (keyword LIKE '%Superman%' AND category = 'B');

What this does is: it returns the row, if keyword is like '%a%' and category = 'A' OR if keyword is like '%supaerman%' and category = 'B'

Your queries did the following

SELECT result FROM myTable
WHERE ( keyword LIKE '%a%' AND category = 'A')
AND   ( keyword LIKE '%Superman%' AND category = 'B'); -- expected 200;  but returned no rows

To return a row here (among other things category in that row had to be 'A' AND 'B'. Since it cannot be both at the same time, no rows were returned.

SELECT distinct result FROM myTable
WHERE ( keyword LIKE '%a%' AND category = 'A')
OR   ( NOT(keyword LIKE '%Superman%') AND category = 'B'); -- expected 10; but returned 10,200..

the NOT(...) in this case made the query return all rows with category equal 'B' where keyword did not contain 'Superman' (plus of course the results from the condition before he OR). ;)

Thanks but that's not an OR. By AND I meant INTERSECTION. See my edit. — pidupuis, Mar 31 '15 at 09:36

score 0 · Answer 3 · answered Mar 31 '15 at 09:36

0

I think you can also take a look at the documentation SIMILAR TO

You can do something like this

SELECT * from myTable where keyword SIMILAR TO '%(oo|ba)%' and category SIMILAR TO '(A)';

answered Mar 31 '15 at 09:36

bbofosu

34
4

PostgreSQL multiple tuples selection in WHERE clause

3 Answers3