CODEWITHSUNDEEP
mysqlsql
B.Allen
B.Allen

Reputation: 79

How to invert or filter out the two employees that work on projects in other departments?

So right now I am so close but right now the result table that is return only shows the employees that I don't want to see and I want to show the employees that only work on projects within their department.

I have joined three tables together and tried using the NOT IN operator but I can't seem to use it correctly?

Here is my code 

SELECT e.FNAME,
       e.LNAME
FROM ballen15db.EMPLOYEE e,
     ballen15db.WORKS_ON w
WHERE e.SSN=w.ESSN
  AND EXISTS
    (SELECT *
     FROM ballen15db.PROJECT p
     WHERE w.PNO=p.PNUMBER
       AND p.DNUM!=e.DNO )
GROUP BY e.SSN;

Expected results

John Smith
Joyce English
Ramesh Rayan
Alicia Zelaya
Ahmad Jabbar
James Borg

Actual Results

Franklin Wong
Jennifer Wallace

enter image description here

Upvotes: 1

Views: 136

Answers (2)

Michael Buen
Michael Buen

Reputation: 39413

This answer is same as Gordon Linoff. every is included in SQL Standard. Postgres has it, MySQL don't. If MySQL has every, Gordon would write it as:

select
    e.SSN,
    e.FNAME,
    e.LNAME
from
    ballen15db.EMPLOYEE e
    join ballen15db.WORKS_ON w on e.SSN = w.ESSN
    join ballen15db.PROJECT p on w.PNO = p.PNUMBER
group by
    e.SSN,
    e.FNAME,
    e.LNAME
having
    every(p.dum = e.dno);

every is not available in MySQL. So I would write the above using MySQL's bit_and instead, so as to preserve the logic. No need to use a combo of inverting the logic, sum and 0.

select
    e.SSN,
    e.FNAME,
    e.LNAME
from
    ballen15db.EMPLOYEE e
    join ballen15db.WORKS_ON w on e.SSN = w.ESSN
    join ballen15db.PROJECT p on w.PNO = p.PNUMBER
group by
    e.SSN,
    e.FNAME,
    e.LNAME
having
    bit_and(p.dum = e.dno);

If your RDBMS doesn't have every, bool_and nor bit_and, and you want to avoid the inversion of logic (sum approach), you can use min instead of sum:

select
    e.SSN,
    e.FNAME,
    e.LNAME
from
    ballen15db.EMPLOYEE e
    join ballen15db.WORKS_ON w on e.SSN = w.ESSN
    join ballen15db.PROJECT p on w.PNO = p.PNUMBER
group by
    e.SSN,
    e.FNAME,
    e.LNAME
having
    min(p.dum = e.dno) = 1

The logic works, that if a department from employee's projects is different from employee's department, it would result to false/0, then min would return 0. If a department from employee's projects is same as employee's department, it would result to true/1, then if all the departments from employee's projects are same as employee's department, then min would return 1.

Of course, if using MySQL it's better to use bit_and, it has more semantic than min and 1 combo, due to _and part, and it has much more semantic than the combo of inverted logic, sum and 0. bit_and is a bit readable, pun :)

But essentially the zero or one indicates Boolean values used to check if something true or not in SQL? -- B. Allen

Yes, but it's mostly due to lack of proper boolean support of those SQL databases. Most SQL databases that lacks proper boolean support, uses zero or one instead. Postgres has a proper boolean support.

In MySQL, its boolean is essentially just an integer. tinyint(1) to be exact. You can think of MySQL's tinyint(1) is leading a double life, it's both an integer and a boolean.

Though MySQL (like SQL Server) doesn't have real boolean, at least MySQL does a better job than SQL Server as MySQL allows its tinyint to act as a boolean.

So aside from being able to do this in MySQL:

select * from hero where hero.can_fly = 1

You can also do the following in MySQL, so your query now feels like natural English, which is what a language with good boolean support should provide:

select * from hero where hero.can_fly

Whereas in SQL Server you can't do where hero.can_fly, must do where hero.can_fly = 1.

Postgres' boolean will only allow the following. Postgres encourages the users to use the proper boolean expression. It also feels like expressing thought in natural language.

where hero.can_fly

where not hero.can_fly

Postgres won't allow this code to run:

where hero.can_fly = 1

where hero.can_fly = 0

On first impression, it looks like MySQL offers the most flexibility. Some would say that it's not a bug, it's a feature. However, MySQL won't flag the followings as wrong codes, let's say a user mistyped a value. MySQL will happily run the code despite the boolean expression having an invalid value.

where hero.can_fly = 2

where hero.can_fly = -1

So it's a bug.

I also assume that min would be the minimal value returned back if the condition is true or false? -- B. Allen

Correct. On MySQL, a true expression maps to 1, a false maps to zero. So if there is at least one expression from rows that yields a false result, min would return 0. If all rows yields a true result, min would return 1.

Tests: https://www.db-fiddle.com/f/spibD9EhCVKHbnyu5GjCV8/4

Good read:

https://blog.jooq.org/2014/12/18/a-true-sql-gem-you-didnt-know-yet-the-every-aggregate-function/

http://www.anicehumble.com/2019/04/not-every-rdbms-has-every.html

From twitter:

'Programming languages teach you not to want what they don't provide.' -- Paul Graham

However, for Postgres, it teaches its users to want other RDBMSes to provide what Postgres already provided.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270301

First, learn to use proper JOIN syntax. Then use GROUP BY and HAVING for your logic:

select e.SSN, e.FNAME, e.LNAME 
from ballen15db.EMPLOYEE e join
     ballen15db.WORKS_ON w
     on e.SSN = w.ESSN join
     ballen15db.PROJECT p 
     on w.PNO = p.PNUMBER 
group by e.SSN, e.FNAME, e.LNAME 
having sum(p.dum <> e.dno) = 0;

The having counts the number of times that the department numbers differ. The = 0 say that no such rows exist.

Upvotes: 2

Related Questions