CODEWITHSUNDEEP
mysqlsql
Noobz
Noobz

Reputation: 23

No result on Query in MySQL

Please Help! I have 3 tables: 

 employee, 
 assign, 
 vehicle.

Only 2 employees can occupy 1 car. I need to select all employees that are not housing any vehicle.

Query:

select EID,
    Employee_name
from employee
where EID not in (
        select user1
        from assign
        )
    and EID not in (
        select user2
        from assign
        )

this query works if all the cells in "user1" or "user2" are filled. If the assign table has a null value for "user1" or "user2"

Assign Table values

enter image description here

the query output is empty

Query result

enter image description here

If fill it in with a value, it will return all EID(s) and employee_name(s) that are not in User1 and User2 in the assigned table. AKA it works normally if all user1 and user2 are filled. How do I remake this query in such a way that it will display all available employees regardless if the assign.user1 or assign.user2 is empty.

Upvotes: 2

Views: 95

Answers (3)

jzeta
jzeta

Reputation: 397

Both answers work fine. If you want to learn more on why your query does not work, here is a short explanation.

The problem comes from how SQL treats NULL values. SQL is not based on a two valued logic, but on a three valued logic: namely, TRUE, FALSE and UNKNOWN. In your query, whenever EID is compared with a NULL value in either the user1 or user2 column, the result is neither TRUE nor FALSE, but UNKNOWN.

Another way out of these is to use the functions COALESCE() or IFNULL():

w3schools on coalesce(), ifnull() and others

For more info on all these, you can check the oracle documentation, among other sources:

oracle documentation on nulls

Hope it helps!

Upvotes: 1

Karan Shah
Karan Shah

Reputation: 1324

You can use NOT NULL as well

SELECT eid, employee_name
FROM employee
WHERE eid not in (
  SELECT user1
  FROM assign
  WHERE user1 IS NOT NULL
  UNION
  SELECT user2
  FROM assign
  WHERE user2 IS NOT NULL
)

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use not exists:

select eid,
    employee_name
from employee e
where not exists (
        select 1
        from assign a
        where e.eid in (a.user1, a.user2)
        );

Upvotes: 1

Related Questions