CODEWITHSUNDEEP
sqlsql-serversql-server-2005
SmartestVEGA
SmartestVEGA

Reputation: 8899

Where clause in where - version 2 :-)

i need to do this :

There is a table called table1 it has a employee id column,status column which has values 1 and 0 only and a department column with values 100,101,102.

i want to list all employeeid with the status = 0

from the above list i want to filter the employees which having (department=100 and whose status=1)

Extended question:

i want to include only whose department is 100 and status =1 from the list of status=0 employees

Please help me

Upvotes: 1

Views: 194

Answers (5)

Matt Rogish
Matt Rogish

Reputation: 24873

Probably something like this, if I'm understanding you correctly.

SELECT *
  FROM emp_dept e1
 WHERE status = 0
   AND EXISTS( SELECT *
                 FROM emp_dept e2
                WHERE e2.id     = e1.id
                  AND e2.status = 1
                  AND e2.dept   = 100 )

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

If you have multiple rows with the same EmployeeID (just guessing here), then you may want to try the following query.

Select EmployeeId
From   Table1
Group By EmployeeId
Having Count(Case When Status = 0 Then 1 End) > 0
       And Count(Case When Status = 1 And Department = 100 Then 1 End) > 0

Upvotes: 0

CResults
CResults

Reputation: 5105

I'm guessing you've phrased it incorrectly and want OR instead of AND, try this..

select * 
  from table1
 where (status=0) or (status=1 and department=100)

Edit

If you only want the employeeid then do this

 select employeeid
   from table1
  where (status=0) or (status=1 and department=100)

Edit No.2

Covering all bases, if you want to find all employees who don't have a status of 0, or a status of 1 and department of 100 then try this!!

 select employeeid
   from table1
  where not (status=0) and not (status=1 and department=100)

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 51000

I'm guessing that you've left out an important part of the problem statement: each employeeid can be in table1 more than one time. I'm assuming that you want employees with one record indicating status 0 and a second record indicating department 100 / status 1.

If so, try this:

SELECT * FROM Table1 AS E0
   INNER JOIN Table1 AS D100 
   ON E0.EmployeeID = D100.EmployeeID
   WHERE E0.Status = 0 AND D100.Department = 100 AND D100.Status = 1

If I understand your question correctly, this should approach what you want.

Upvotes: 1

JonH
JonH

Reputation: 33163

This doesnt make sense...you want to filter all employees whose status is 0. And once you have filtered these records you want to refilter that original filter to include all employees which have department 100 (which is still ok), but then you want the status to be 1. How can that be given that you stated in your original request you only wanted employees with 0 status???

Think about it and take it from a person trying to answer your question. You have 10 records, half of them have employees with a 0 status and the other half have a 1 status.

Jon1  1
Jon2  1
Jon3  1
Jon4  1
Jon5  1

JOn6  0
Jon7  0
Jon8  0
Jon9  0
Jon10 0

So you want all employees with 0:

JOn6  0
Jon7  0
Jon8  0
Jon9  0
Jon10 0

Simple enough : SELECT blah FROM Employees WHERE Status=0

Now you want another query which has their department number of 100 but have a status of 1. Well how can you find one that has a status of 1 when you filtered for a status of 0????

You probably want an OR condition:

SELECT blah FROM myTable WHERE EmpStatus=0 OR (EmpStatus=1 AND Dept=100)

Which clearly is a different question...

Upvotes: 0

Related Questions