tibsar tibsar - 6 months ago 11
SQL Question

Exclude row based on condition

I currently have a table called

_01769_jobs
:

_01769_jobs

From this table, I want to select all of the rows where the
user_id
is not equal to
163084
and the status is equal to
QC_COMPLETE
:

SELECT * FROM _01769_jobs WHERE user_id != 163084 AND status = 'QC_COMPLETE';


Result of Query

From here, I want to further refine these results to exclude any entries where the
job_id
is a
job_id
listed in the result of the
blind_qc_get_worked_on_jobs_by_project_by_user
function.

Calling the function results in the following:

SELECT * FROM blind_qc_get_worked_on_jobs_by_project_by_user(1769, 163084);


result from function call

I now want to modify my initial query to exclude the rows whose
job_id
's are returned from the function call.

I've tried:

SELECT * FROM _01769_jobs jobs left join blind_qc_get_worked_on_jobs_by_project_by_user(1769, 163084) bqc_jobs on jobs.job_id = bqc_jobs.job_id WHERE jobs.user_id != 163084 AND bqc_jobs.user_id != 163084 AND jobs.status = 'QC_COMPLETE' ;


and a few variations but am not quite sure how to get the desired results.

Answer

In most forms of SQL, you would use a NOT IN clause for this.

SELECT * FROM _01769_jobs 
WHERE user_id != 163084 
  AND status = 'QC_COMPLETE'
  AND job_id NOT IN (
      SELECT job_id
      FROM blind_qc_get_worked_on_jobs_by_project_by_user(1769, 163084)
  )

Postgres seems to be no exception - http://www.postgresqltutorial.com/postgresql-in/

If you need this for varying user_id and product_id values, different database engines use different features for this. Postgres may be able to do it with a lateral join - http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/

If your database engine does not have a lateral-join or cross-apply, you will need to rework and massage the code of the function into your particular select.