tibsar tibsar - 2 years ago 100
SQL Question

Exclude row based on condition

I currently have a table called



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

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
is a
listed in the result of the

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
'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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download