samedi 9 mai 2015

Prepare single where clause

I have the following table with two fields namely a and b as shown below:

create table employe
(
    empID varchar(10),
    department varchar(10)
);

Inserting some records:

insert into employe values('A101','Z'),('A101','X'),('A101','Y'),('A102','Z'),('A102','X'),
             ('A103','Z'),('A103','Y'),('A104','X'),('A104','Y'),('A105','Z'),('A106','X');


select * from employe;

empID   department
------------------
A101    Z
A101    X
A101    Y
A102    Z
A102    X
A103    Z
A103    Y
A104    X
A104    Y
A105    Z
A106    X

Note: Now I want to show the employee who is only and only belongs to the department Z and Y. So according to the condition the only employee A103 should be displayed because of he only belongs to the department Z and Y. But employee A101 should not appear because he belong to Z,X, and Y.

Expected Result:

If condition is : Z and Y then result should be:

empID
------
A103

If condition is : Z and X then result should be:

empID
------
A102

If condition is : Z,X and Y then result should be:

empID
------
A101

Note: I want to do it in the where clause only (don't want to use the group by and having clauses), because I'm going to include this one in the other where also.

Aucun commentaire:

Enregistrer un commentaire