Select the rows in which a column should have the same value for all rows


I want to write a MySQL script the accomplishes the following, I have an Orders table with data like this -

CustomerNumber | OrderNumber | Fullfilled
001              10001         T
001              10002         T

002              10003         F
002              10004         T

003              10005         T
003              10006         T
003              10007         T

004              10008         T

I want to create a query that will list only the customer numbers that have more than 1 order, and all of their orders have Fullfilled = "T".

The query against the above data would result in a list of CustomerNumbers of - "001,003".

Try this:

SELECT CustomerNumber
FROM mytable
GROUP BY CustomerNumber
HAVING COUNT(DISTINCT OrderNumber) > 1 AND SUM(Fullfilled <> 'T') = 0

The query uses two predicates in its HAVING clause:

  • The first one returns customer numbers being related to more than one distinct order numbers
  • The second one filters out customer numbers being related to at least one Fullfilled = 'T' record.

    Demo here