1

I want to drop rows from a query result based on the combination of two columns e.g.

My table after join is

-- JOIN of person and flag table based on person_id

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 10             1               IGNORED
1                 20             1               FAVORITE
2                 20             2               FAVORITE
2                 10             2               IGNORED 
3                 10             3               FAVORITE 

Now, I want remove/drop all persons who have "ignored" the query 10.

So remove rows where query_id = 10 and flag = 'IGNORED'.

The resulting table should be:

Person.Person_id  Flag.Query_id  Flag.Person_id  Flag.query_flag
1                 20             1               FAVORITE
2                 20             2               FAVORITE 
3                 10             3               FAVORITE 

So I want to drop rows from the result based on the combination of two columns.

I know that I can select a row based on the combination of two columns as:

SELECT * 
FROM  -- query with joins here
WHERE Flag.Query_id = 10 and Flag.query_flag = 'IGNORED'

I tried doing something like:

 SELECT * 
 FROM  -- query with joins here
 WHERE NOT (Flag.Query_id = 10 AND Flag.query_flag = 'IGNORED')

I see here that NOT is applied to the columns individually and not to a combination of conditions.

How can I achieve my result without a subquery?

closed as unclear what you're asking by Colin 't Hart, hot2use, RDFozz, mustaccio, Vérace May 14 '18 at 19:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • @ypercubeᵀᴹ SELECT * FROM my_table WHERE NOT (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED'); does not give the desired result. It drops all rows where query_id = 10 even if the flag is "FAVORITE". It is not working on the combination of columns – gogogaga May 14 '18 at 8:42
  • Discussion moved to chat. – ypercubeᵀᴹ May 14 '18 at 8:49
  • If you added table definitions showing data types and constraints, your actual query and your version of Postgres, this would actually be an very interesting question. Boolean logic with ROW values isn't trivial. – Erwin Brandstetter May 15 '18 at 22:56
2

To be precise, to ...

remove rows where query_id = 10 and flag = 'IGNORED'

... you need something like this:

SELECT * 
FROM  ...  -- you are hiding a more complex query here!
WHERE (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED') IS NOT TRUE;

In a WHERE clause, only TRUE qualifies. Rows with FALSE or NULL are excluded.

What you display:

...
WHERE NOT (Flag.Query_id = 10 and Flag.query_flag = 'IGNORED');

... would also exclude rows with NULL values in Flag.Query_id or Flag.query_flag - not exactly what you asked for. Even if the columns are defined NOT NULL, NULL values can be introduced with an OUTER JOIN or similar in your undisclosed query.

(The sample rows you display are not affected, since there are no NULL values.)

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