-1

I'm having trouble with a SQL expression that doesn't do what I thought it should do.

I have jobs that belong to clients and also that jobs may pertain to a certain project or either the project would be null (if they pertain to a project in the column project_id it appears an int).

First of all I'm taking all jobs that are available but I need to remove certain clients when the project is null.

I'm doing this in the WHERE clause:

AND (p4_.uid NOT IN (722, 4736, 1041, 735) AND s3_.project_id IS NULL)

But SQL is taking it as two separated AND like this

AND p4_.uid NOT IN (722, 4736, 1041, 735) 
AND s3_.project_id IS NULL

And for example is not showing any from the client with uid 735 even if the client have jobs with a project id assigned.

forpas
  • 160,666
  • 10
  • 38
  • 76
Edw4rd
  • 147
  • 1
  • 9
  • 1
    add sample data and the complete query – Indrakumara Oct 21 '21 at 16:43
  • 1
    Let me rephrase your question. You have `AND (a > 1 AND b < 2)`, and you are wondering why it behaves as `AND a > 1 AND b < 2`. How should it behave instead? – GSerg Oct 21 '21 at 16:51
  • @GSerg 1º case it will take the job if a is > 1 AND b < 2 at the same time, but in 2º case it take every job that have a>1 and every job that have b<2 even if it's not at the same time. – Edw4rd Oct 21 '21 at 16:57
  • @Edw4rd The outer `AND` in the `AND (...)` does not even affect the contents of the `(...)`. You literally have `a > 1 AND b < 2`, and you are wondering why it doesn't behave as `a > 1 OR b < 2`. In which situation would an `AND` mean "or"? – GSerg Oct 21 '21 at 17:08

1 Answers1

1

but I need to remove certain clients when the project is null

This requirement should be written as:

AND NOT (p4_.uid IN (722,4736,1041,735) AND s3_.project_id IS NULL)

or the equivalent:

AND (p4_.uid NOT IN (722,4736,1041,735) OR s3_.project_id IS NOT NULL)
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks, could you explain to me why is this not working: `AND (p4_.uid NOT IN (722,4736,1041,735) AND s3_.project_id IS NOT NULL)` – Edw4rd Oct 21 '21 at 17:04
  • @Edw4rd Each of the 2 boolean expressions is applied for every row of the dataset and they both must return TRUE in order to get TRUE by their combination because of the operator AND between them. `p4_.uid NOT IN (722,4736,1041,735)` filters out all the uids in the parenthesis regardless of the project_id. Also. `s3_.project_id IS NOT NULL` filters out all uids with null project_id regardless of uid. – forpas Oct 21 '21 at 17:11