CODEWITHSUNDEEP
mysqlsql
Kapila Perera
Kapila Perera

Reputation: 857

Select All departments which has employeeId is null and is not null?

How can I select All the departments which has null values of employeeId and not null values of employeeId?

Department

id       employeeID
 1           1
 1           null
 2           2
 3           null

The result should be department id - 1

Upvotes: 1

Views: 2938

Answers (8)

Kristian Ray
Kristian Ray

Reputation: 76

I do not about department table structure.

You can try below query.

For Not null values of employee id

      SELECT id 
        FROM tablename 
       WHERE employeeID is not null 
    GROUP BY employeeID 
   INTERSECT
      SELECT id 
       FROM tablename 
      WHERE employeeID is null 
   GROUP BY employeeID

You can try this solution.

Upvotes: 0

Pawan Keshri
Pawan Keshri

Reputation: 13

You can try this:

select id, employeeId from department order by id;

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Do a GROUP BY. Use HAVING to return id's where the number of employeeID's are greater than 0 and also less than the total number of that id rows.

select id
from department
group by id
having count(employeeID) > 0
   and count(employeeID) < count(*)

(count(employeeID) counts non-null rows. count(*) counts all rows.)

Upvotes: 0

Rupesh Pandey
Rupesh Pandey

Reputation: 71

Try This, Intersect will do the trick

select * from (
SELECT id from Department WHERE employeeID is not null
INTERSECT
SELECT id from Department WHERE employeeID is null
)x

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44931

select      id
from        department
group by    id
having      count(employeeId) between 1 and count(*)-1

count(employeeId) counts non-NULL employeeId

  • If it is > 0 it means we have at least one NOT NULL value.
  • If it is < count(*) it means we have at least one NULL value.

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Simply go with EXISTS

SELECT DISTINCT T.id
FROM TAB T
WHERE EXISTS (SELECT 1 FROM TAB T1 WHERE T.id = T1.id AND T1.employeeID IS NULL)
AND EXISTS  (SELECT 1 FROM TAB T1 WHERE T.id = T1.id AND T1.employeeID IS NOT NULL)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562280

So you want department id's for which there are some null and some non-null employeeid's?

You can use GROUP BY to reduce the set of rows for each department id.

Then filter the groups with HAVING such that there's a nonzero number of entries in the group with NULL employeeid, and also a nonzero number of entries in the group with non-NULL employeeid.

SELECT id
FROM Department
GROUP BY id
HAVING COUNT(CASE WHEN employeeID IS NULL THEN 1 END) > 0 
  AND  COUNT(CASE WHEN employeeID IS NOT NULL THEN 1 END) > 0;

Upvotes: 1

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Try this:

SELECT id, employeeID from tablename GROUP BY id, employeeID;

This will GROUP BY id, employeeID and return you all the records.

Upvotes: 0

Related Questions