CODEWITHSUNDEEP
mysqlsql
Rahul shah
Rahul shah

Reputation: 185

How to query where same id has different value in specific column Mysql

I have below mentioned table:

ID      Val1
1       AVD1R
1       ART1R
2       CFD4E
3       DER1R
3       DER1F

I want to fetch those records where same ID is more than one time with different Val1.

Required Output:

ID      Val1
1       AVD1R
1       ART1R
3       DER1R
3       DER1F

I have tried this: select id, Val1 from Table1 where count(Val1)>1 group by id; But it didn't work.

Upvotes: 0

Views: 136

Answers (3)

saravanatn
saravanatn

Reputation: 630

I don't know what are the others columns are.

But in SQL Server:

select distinct a.id,val1 from
(
select id,val1
from different
)a
inner join
(
select id,count(id) as cnt
from different 
group by id
having count(*)>1
 ) b
  on  a.id=b.id

Upvotes: 0

BinaryPatrick
BinaryPatrick

Reputation: 512

SELECT a.* FROM (
    SELECT ID, Val1, COUNT(*) AS Cn FROM Table1 GROUP BY ID, Val1) AS a
LEFT JOIN (
    SELECT ID, COUNT(*) AS Cn FROM Table1 GROUP BY ID
) AS b ON a.ID = b.ID
WHERE a.Cn <> b.Cn

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

Sorry, i have change my answer to:

SELECT t1.* FROM Table1 t1
INNER JOIN Table1 t2 
  ON t1.id=t2.id AND t1.VAl1 <> t2.Val1;

Sample

MariaDB [bernd]> select * from Table1;
+----+-------+
| id | VAl1  |
+----+-------+
|  1 | AVD1R |
|  1 | ART1R |
|  2 | CFD4E |
|  3 | DER1R |
|  3 | DER1F |
+----+-------+
5 rows in set (0.00 sec)

MariaDB [bernd]> SELECT t1.* FROM Table1 t1
    -> INNER JOIN Table1 t2 ON t1.id=t2.id AND t1.VAl1 <> t2.Val1;
+----+-------+
| id | VAl1  |
+----+-------+
|  1 | ART1R |
|  1 | AVD1R |
|  3 | DER1F |
|  3 | DER1R |
+----+-------+
4 rows in set (0.00 sec)

MariaDB [bernd]>

Upvotes: 1

Related Questions