MySQL - Compare rows within group

Question

Using MySQL, I need to compare rows within the same group and return rows that don't match according to a particular field.

For example, say I have the following table:

I need to return all rows that have the same id, but have differing note values. The resulting output for the above table should be:

How would I go about accomplishing this goal?

Thanks in advance!

score 1 · Accepted Answer · answered Aug 19 '21 at 23:45

1

you can use a subquery for this

SELECT DISTINCT `cust`, `note` FROM tabl2 WHERE cust IN (SELECT Cust FROM tabl2  GROUP BY cust HAVING COUNT(DISTINCT note) > 1)

cust | note
---: | :---
   2 | foo1
   2 | foo2
   3 | foo3
   3 | foo4

db<>fiddle here

answered Aug 19 '21 at 23:45

nbk

Don't use quotation markup for code blocks (unless you're quoting a text that contains code). – Barmar Aug 19 '21 at 23:46
And don't put table markup inside a code block. – Barmar Aug 19 '21 at 23:47
@Barmar the block comes from dbfiddle and it is ok to do so – nbk Aug 19 '21 at 23:48
But if you take the table out of code formatting, you'll get a nicely formatted table (as in the question). – Barmar Aug 19 '21 at 23:49

1 Answers1