Showing all rows for keys with more than one row

Question

Table kal
id integer primary key
init char 4 indexed
job char4

id  init job
--+----+------
1 | aa | job1
2 | aa | job2
3 | bb | job1
4 | cc | job3   
5 | cc | job5

I want to show all rows where init has more than one row:

id  init job
--+----+------
1 | aa | job1
2 | aa | job2
4 | cc | job3   
5 | cc | job5

I tried:

select * from kal where init in (select init from kal group by init having 
count(init)>2);

Actually, the table has 60000 rows, and the query was

count(init)<40, 

but it takes a humongous amount of time, phpmyadmin and my patience runs out.

Both

select init from kal group by init having count(init)>2)

and

select * from kal where init in ('aa','bb','cc')

runs in "no time", less than 0.02 seconds.

I've tried different subqueries, but all takes "infinite" time, more than a few minutes; I've actually never let them finish.

Answer 1

Here's an example, and you can see it in action:

Query

SELECT a.id, a.init, a.job
FROM kal a
INNER JOIN
  (SELECT init, COUNT(init) 
  FROM kal
  GROUP BY init
  HAVING COUNT(init) > 1) b ON b.init = a.init

Result

| ID | INIT |  JOB |
--------------------
|  1 |   aa | job1 |
|  2 |   aa | job2 |
|  4 |   cc | job3 |
|  5 |   cc | job5 |