Menu
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.
611
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
Here, we will see how to query to find the employees whose departments are not assigned by using the following SQL query as follows. SELECT* FROM employee WHERE emp_dept IS NULL; Output : In this table, all the employee records whose department is NULL value are obtained.
Syntax: SELECT EMPLOYEE_NAME, DEPARTMENT_NAME FROM COMPANY WHERE DEPARTMENT_NAME IN (SELECT DEPARTMENT_NAME FROM COMPANY GROUP BY DEPARTMENT_NAME HAVING COUNT(*)<2);
Display ename who are working in the sales dept. Ans: select ename from emp where deptno= (select deptno from dept where dname='SALES'); Display employee name, deptname, salary, and comm.
This is a Relational Division with no Remainder (RDNR) problem. See this article by Dwain Camps that provides many solution to this kind of problem.
First Solution
SQL Fiddle
SELECT empId FROM ( SELECT empID, cc = COUNT(DISTINCT department) FROM employe WHERE department IN('Y', 'Z') GROUP BY empID )t WHERE t.cc = 2 AND t.cc = ( SELECT COUNT(*) FROM employe WHERE empID = t.empID ) Second Solution
SQL Fiddle
SELECT e.empId FROM employe e WHERE e.department IN('Y', 'Z') GROUP BY e.empID HAVING COUNT(e.department) = 2 AND COUNT(e.department) = (SELECT COUNT(*) FROM employe WHERE empID = e.empId) Without using GROUP BY and HAVING:
SELECT DISTINCT e.empID FROM employe e WHERE EXISTS( SELECT 1 FROM employe WHERE department = 'Z' AND empID = e.empID ) AND EXISTS( SELECT 1 FROM employe WHERE department = 'Y' AND empID = e.empID ) AND NOT EXISTS( SELECT 1 FROM employe WHERE department NOT IN('Y', 'Z') AND empID = e.empID ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
