Understanding DOES NOT EXIST

Question

I'm trying to hang NOT EXISTS and I'm having problems. Let's say I have 2 tables.

 Employees: +------+------+ | eid | name | +------+------+ | 1 | Bob | | 2 | Alice| | 3 | Jill | +------+------+ Transactions: +----------+----------+----------+-----------+ | tid | eid | type | amount | +----------+----------+----------+-----------+ | 1 | 1 | Deposit | 50 | | 2 | 1 | Open | 500 | | 3 | 3 | Open | 200 | | 4 | 2 | Withdraw | 25 | | 5 | 2 | Open | 100 | +----------+----------+----------+-----------+ 

Let's say I want to find the names of all the employees who did not open any account in the amount of $ 250 or higher. This means that I only need the lines in which the employee opened an account with an amount of <$ 250. Right now I have something like this ...

 SELECT name FROM Employees e WHERE NOT EXISTS ( SELECT * FROM Transactions t WHERE t.type <> 'Open' AND t.amount >= 250 AND t.eid = e.eid); 

This is obviously wrong, and I really don't understand why.

Answer 1

You need to combine EXISTS with NOT EXISTS , since you only need the lines in which the employee opened an account for $ 250:

 SELECT name FROM Employees e WHERE EXISTS ( SELECT 1 FROM Transactions t WHERE t.amount < 250 AND t.type='Open' AND t.eid = e.eid) AND NOT EXISTS ( SELECT 1 FROM Transactions t WHERE t.amount >= 250 AND t.eid = e.eid); 

You need EXISTS to make sure that only an employee who has an open account with a quantity of <250 in general is returned. NOT EXISTS is required to ensure that employees who have additional accounts with a quantity of> 250 are not included.

Here's a demo of sql-sciddle

Answer 2

The only problem I see is that you used <> for the transaction type, not =

 SELECT name FROM Employees e WHERE NOT EXISTS ( SELECT null FROM Transactions t WHERE t.transaction_type = 'Open' AND t.amount >= 250 AND t.eid = e.eid); 

After you have edited your question, the answer will be as follows:

 SELECT name FROM Employees e WHERE EXISTS ( SELECT null FROM Transactions t WHERE t.transaction_type = 'Open' AND t.amount < 250 AND t.eid = e.eid); 

Answer 3

I would recommend using LEFT JOIN instead of choosing sub.

 SELECT name FROM Employees e LEFT JOIN Transactions t ON e.eid = t.eid WHERE t.tid IS NULL OR t.type <> 'Open' OR t.amount <= 250; 

This combines all transaction records and then includes only records in which the transaction does not exist, the user has a transaction that is not open, or the amount does not match reewqred $ 250