CODEWITHSUNDEEP
mysqlsql
x-code
x-code

Reputation: 608

mysql not exist in other table

my emp table is like this

+------+------------+
| emp  | emp_status |
+------+------------+
| 001  | w          |
| 002  | nw         |
| 003  | w          |
| 004  | w          |
+------+------------+

and salary table is

+------+------------+
| emp  | month      |
+------+------------+
| 001  | Dec2013    |
| 002  | Dec2013    |
| 003  | Dec2013    |
| 004  | Dec2013    |
| 001  | Jan2014    |
| 002  | Jan2014    |
| 003  | Jan2014    |
| 004  | Jan2014    |
| 001  | Feb2014    |
| 004  | Feb2014    |
+------+------------+

so for the month of Feb2014 i want to get the emp who wasn't paid

+------+------------+
| emp  | emp_status |
+------+------------+
| 003  | w          |
+------+------------+

i tried with 

SELECT * FROM emp WHERE NOT EXISTS (SELECT emp FROM salary where month_sal ='Feb2014')

and i want to use emp_status(with 'w') aswell... any hint..

Upvotes: 0

Views: 58

Answers (2)

Alan
Alan

Reputation: 3002

The following should work:

SELECT * 
FROM emp
WHERE emp_status = 'w'
  AND emp NOT IN (SELECT emp FROM emp WHERE month_sal ='Feb2014')

It looks slightly odd having a field name the same as the table name, but hopefully that's correct.

Upvotes: 0

Arion
Arion

Reputation: 31239

You are missing to connect the where clause in the EXISTS to the outer query like this:

SELECT * FROM emp 
WHERE NOT EXISTS 
(
    SELECT NULL 
    FROM salary 
    where salary.month_sal ='Feb2014'
    AND salary.emp=emp.emp
)
AND emp.emp_status='w'

What you were starting to write looks like a IN. You can also do that like this:

SELECT * FROM emp 
WHERE NOT emp.emp IN 
(
    SELECT salary.emp 
    FROM salary 
    where salary.month_sal ='Feb2014'
)
AND emp.emp_status='w'

Upvotes: 1

Related Questions