CODEWITHSUNDEEP
mysqlsql
Dilukshan Mahendra
Dilukshan Mahendra

Reputation: 3398

MySQL Select the rows having same column value

I need to obtain the rows which having same column value from the following table, I tried it the following way, but it gives me a single row only.

select *
from employee e
group by e.empsalary
having count(e.empsalary) > 1

Table employee

enter image description here

Please suggest me a way.

Upvotes: 5

Views: 8315

Answers (4)

Juhi
Juhi

Reputation: 270

select * from employee where empsalary IN(select empsalary from employee  group by empsalary having count(empsalary ) > 1)

Try This.It gives 2 rows as you want.

Demo: http://sqlfiddle.com/#!2/d75d9/6

Upvotes: 2

Phil
Phil

Reputation: 164730

Something like this perhaps

SELECT * FROM employee a
WHERE EXISTS (
    SELECT 1 FROM employee b
    WHERE a.empsalary = b.empsalary
    AND a.empid <> b.empid
);

Demo - http://sqlfiddle.com/#!2/d75d9/4

Upvotes: 2

xdazz
xdazz

Reputation: 160833

Use an inner join to join with your query posted.

select A.* from employee A
inner join (
  select empsalary
  from employee 
  group by empsalary
  having count(*) > 1
) B ON A.empsalary = B.empsalary

Upvotes: 3

newfurniturey
newfurniturey

Reputation: 38416

You should be able to accomplish this by joining the table to itself:

SELECT
    a.*
FROM
    employee a
    JOIN employee b
        ON a.empsalary = b.empsalary
        AND a.empid != b.empid

Upvotes: 8

Related Questions