Geek Answers Handbook

At least one X, but not Ys Query

I sometimes came across this pattern, and I did not find a terribly satisfactory way to solve it.

Say I have a table employeeand a table review. Each employee can have more than one review. I want to find everyone employeewho has at least one “good” review, but no “bad” reviews.

I did not understand how to make the subqueries work without knowing the identifier of the employee before starting work, and I did not understand the correct combination of joins for this to happen.

Is there a way to do this WITHOUT stored procedures, functions, or connecting to a data server? I started working with them, but I'm sure there is another way.

+4
source share
4 answers

, ( rating, , , , ). .

1:

select distinct e.EmployeeId, e.Name
from employee e
left join reviews r1 on e.EmployeeId = r1.EmployeeId and r1.rating = 'good'
left join reviews r2 on e.EmployeeId = r2.EmployeeId and r1.rating = 'bad'
where r1.ReviewId is not null --meaning there at least one
and r2.ReviewId is null --meaning there no bad review

2:

select e.EmployeeId, max(e.Name) Name
from employee e
left join reviews r on e.EmployeeId = r.EmployeeId
group by e.EmployeeId
having count(case r.rating when 'good' then 1 else null end) > 0
and  count(case r.rating when 'bad' then 1 else null end) = 0

SQL ANSI, , RDBMS, SQL ANSI ( ).

@oneday, MS Access ( , ).

( ): MS Access RDBMS. . , (Oracle, SQL Server, Firebird, PostGreSQL, MySQL, ), . .

+7

- A, B - ( "" ) "--". , 5, MS Sql 2005 .

, MSSQL 2000 , 5 , MS Sql 2005 2008. , MySQL, , .

. IN employee .

SELECT EE.*
FROM   employee EE
WHERE
    EE.EmpKey IN (
      SELECT RR.EmpKey
      FROM   review RR
      WHERE  RR.EmpKey = EE.EmpKey
        AND  RR.ScoreCategory = 'good'
    ) 
  AND
    EE.EmpKey NOT IN (
      SELECT  RR.EmpKey 
      FROM    review RR
      WHERE   RR.EmpKey = EE.EmpKey
        AND   RR.ScoreCategory = 'bad'
    )
+5

. , "" "" .

, review classification, "good" "bad".

:

SELECT employee.*
FROM employee
JOIN review 
 ON employee.id=review.employee_id
GROUP BY employee.id
HAVING SUM(IF(classification='good',1,0))>0 -- count up #good reviews, > 0
   AND SUM(IF(classification='bad',1,0))=0  -- count up #bad  reviews, = 0.
+1
SELECT ???? FROM employee,review
WHERE employees.id = review.id
GROUP BY employees.id
HAVING SUM(IF(review='good',1,0)) > 1 AND SUM(IF(review='bad',1,0)) = 0
+1

More articles:


All Articles