MYSQL Select within same table

Question

I have a table that I need a MYSQL Select statement so I can find the corrupt data and manually fix it.

Sample table

TABLE_A
id | type | value1 | value2
 1 |  10  | 123    | 987  
 2 |  10  | 123    | 987
 3 |  10  | 123    | 789
 4 |  20  | 123    | 987
 5 |  20  | 456    | 987
 6 |  30  | 123    | null
 7 |  30  | 123    | null
 8 |  40  | 123    | 987

I need a select statement that will list records that if they have the same "type" and value1 is not the same and/or value2 not the same.

For Example

ID 1,2,3 - will be displayed because value2 is different in id 3 and they have the same "type"
ID 4,5 will be displayed because value1 is different and they have the same "type"
ID 6,7 will NOT be displayed because value1 and value2 are the same for the same "type"
ID 8 will NOT be displayed because there is only one with this type.

I have been trying to get my head around this for days and need some help. Thanks

type is specified ? ( e.g type= '$type' or query must group results by type ? ) — Hamid
– Hamid, Commented Jul 3, 2011 at 6:35

Amber · Accepted Answer · 2011-07-03 06:30:16Z

5

SELECT a.id as problem_id
FROM TABLE_A as a JOIN TABLE_A as b
ON a.type = b.type
WHERE a.value1 <> b.value1 OR a.value2 <> b.value2
GROUP BY problem_id;

answered Jul 3, 2011 at 6:30

Amber

531k89 gold badges643 silver badges558 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tudor Constantin · Accepted Answer · 2011-07-03 06:30:32Z

0

Try with:

SELECT * FROM TABLE_A WHERE type IN (SELECT type FROM TABLE_A GROUP BY `type`, `value1`, `value2` HAVING count(value1) > 1 OR count(value2) > 1)

answered Jul 3, 2011 at 6:30

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

4 Comments

Matt Dykstra Over a year ago

excellent that work in the test. I will try it in the main database now. Thanks @Tudor!

Tudor Constantin Over a year ago

An upvote and accept answer are a great to show your gratitude :)

Matt Dykstra Over a year ago

Just tested and this does not compare the value1 or value2

Tudor Constantin Over a year ago

It should group by them if they are different, the COUNT function returns the number of different items

Collectives™ on Stack Overflow

MYSQL Select within same table

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related