CODEWITHSUNDEEP
mysqlsql
Aaron
Aaron

Reputation: 75

SQL -- return groups of records where one of the fields does not match amongst all records in the group

In the sample table below, all records with the same O_Id should also have the same price. Obviously, for O_Id's 1 and 3, not all records have the same price.

I'm having difficulty writing a query that would return all records from a table that have the same O_Id as one or more other records, but where the OrderPrice does not match among records with the same O_id.

Sample table:

+------+------------+------------+
| Type |  MySQL     | OrderPrice |
+======+============+============+
| 1    | 2008/11/12 | 1000       |
| 1    | 2008/10/23 | 2000       |
| 2    | 2008/09/02 |  700       |
| 2    | 2008/09/03 |  700       |
| 3    | 2008/08/30 | 2000       |
| 3    | 2008/10/04 | 2100       |
| 3    | 2008/08/30 | 2000       |
| 3    | 2008/10/04 | 2000       |
+------+------------+------------+

Sample result set:

+------+------------+------------+
| Type |  MySQL     | OrderPrice |
+======+============+============+
| 1    | 2008/11/12 | 1000       |
| 1    | 2008/10/23 | 2000       |
| 3    | 2008/08/30 | 2000       |
| 3    | 2008/10/04 | 2100       |
| 3    | 2008/08/30 | 2000       |
| 3    | 2008/10/04 | 2000       |
+------+------------+------------+

Upvotes: 1

Views: 89

Answers (2)

Final Form
Final Form

Reputation: 318

Here is a solution using Exists:

Select * From OrderTable O
Where Exists(
    Select O_Id From OrderTable
    Where O_Id = O.O_Id
    Group By O_Id
    Having Count(Distinct OrderPrice) > 1 )

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

You can get O_Id where there are more than one different prices associated with:

SELECT O_Id
FROM myTable
GROUP BY O_Id
HAVING COUNT(DISTINCT OrderPrice) > 1

and use it as inner statement to get all rows for these O_Ids:

SELECT *
FROM myTable
WHERE O_Id IN (
    SELECT O_Id
    FROM myTable
    GROUP BY O_Id
    HAVING COUNT(DISTINCT OrderPrice) > 1
)

Upvotes: 1

Related Questions