Join the Stack Overflow Community
Stack Overflow is a community of 6.9 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

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 ...

share|improve this question
    
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 9: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 9: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 9:28
    
@pidupuis for expected 200; but returned no rows > try this – w͏̢in̡͢g͘̕ed̨p̢͟a͞n͏͏t̡͜͝he̸r̴ Mar 31 '15 at 10:27
up vote 1 down vote accepted

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:

share|improve this answer
    
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
up vote 0 down vote

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). ;)

share|improve this answer
    
Thanks but that's not an OR. By AND I meant INTERSECTION. See my edit. – pidupuis Mar 31 '15 at 9:36
up vote 0 down vote

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)';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.