Search code examples
sqlpostgresqlredash

SQL WHERE Clausule to get rows depending on the database content

Use case:

  • I have the customer_id and the task_id.

  • The database will always contain registers with a filled customer_id and empty task_id.

  • Sometimes will have the task_id filled. (as the example below)

Example 1 Example 1 - Data Base

SELECT *
  FROM table
 WHERE customer_id = 11422412 
   AND task_id = 28870055

Here I expect to return the last two rows.

Example 2 Example 2 - database

SELECT * 
  FROM table
 WHERE customer_id = 11432515 
   AND task_id = 22256884

Here I expect to return the only empty row.

Question:

How do I create a SQL Query to make sure that, in case the task_id exists in the database, I only return the records with task_id?

Solution

  • You could do something like the following with LIMIT. This will match the empty task_id and the set task_id (if it exists), order them so that the row with non-empty task_id comes first (if it exists), then return only the first one. (NULLS LAST is default sorting behavior in Postgre)

    SELECT *
FROM table
WHERE customer_id = 11432515 
 AND (task_id = 22256884 OR task_id IS NULL)
ORDER BY task_id
LIMIT 1

    I am assuming that you always want exactly one row like in your examples.

    But there are other ways of doing it depending on your specific scenario (if your final query is more complicated than your examples).

    Edited to add another way to handle case where more than one row matches customer_id and task_id:

    SELECT *
FROM table t1
WHERE customer_id = 11432515 
 AND (task_id = 22256884
  OR (
   task_id is null
   AND NOT EXISTS (SELECT * FROM table t2 WHERE t2.customer_id = 11432515 AND t2.task_id = 22256884)
  )
 )

    This doesn't look super elegant, but it should work and you could use it as a starting point at least.