I wanted to display employees that have the same d_id and salary with employees that have commission.

I tried:

SELECT name, d_id, salary
FROM employees
WHERE (d_id, salary) IN (
    SELECT d_id, salary
    FROM employees
    WHERE commission > 0);

Expected output:

Actual output:

CodePudding user response：

Modify your query as the following:

SELECT name, d_id, salary
FROM employees
WHERE (d_id, salary) IN (
    SELECT d_id, salary
    FROM employees
    Group By d_id, salary
    Having Max(commission) > 0 And Count(*) > 1);

Having Max(commission) > 0 ensures that at least one employee have a commision value > 0.

Count(*) > 1 ensures that there is more than one employee with the same d_id, salary.

See a demo.

CodePudding user response：

If your DBMS allows for window functions, you can use the COUNT one with partition on pairs of "<d_id, salary>". This will allow you to count how many existing identical couples are there for each couple. All count values bigger than 1 are the ones you're looking for.

WITH cte AS (
    SELECT *, COUNT(name) OVER(PARTITION BY d_id, salary) AS cnt 
    FROM tab
)
SELECT name, d_id, salary
FROM cte
WHERE cnt > 1

Here's a demo for MySQL 8.0, but most likely will work on most DBMS' supporting window functions.