I have a table
| ID | Name | job | Colour |
|---|---|---|---|
| 1 | Abby | Janitor | pink |
| 2 | Bob | teacher | yellow |
| 3 | Cam | Musician | blue |
| 4 | Bob | teacher | green |
| 5 | Abby | teacher | pink |
And I want to get the Name of those with the same Job but has at least two different colours
So in this case only Bob would get returned
CodePudding user response：
We can use an aggregation approach here:
SELECT Name
FROM yourTable
GROUP BY Name
HAVING MIN(job) = MAX(job) AND
MIN(Colour) <> MAX(Colour);
CodePudding user response：
SELECT Name
FROM TblComb
GROUP BY Name,Job
HAVING COUNT(Colour) > 1;
Result_Set:
