How to use a group by or having clause instead of inner join MYSQL

Question

I wrote this query

SELECT K.ID, K.`TYPE`, K1.`TYPE`,

FROM table K

INNER JOIN table K1

ON K1.ID = K.ID

WHERE K1.`TYPE` = 'ISSN'

AND K.`TYPE` = 'ISBN'

It looks for one object ID having two records, one with type = ISSN and one with ISBN.

These tables are very big and this query takes about ten minutes to run. Is there a faster alternative to using either nested select (on the same table) or group by having syntax

Thank you

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

The GROUP BY version:

SELECT K.ID
FROM table K
WHERE K.`TYPE` IN ('ISSN', 'ISBN')
GROUP BY K.ID
HAVING COUNT(DISTINCT K.`TYPE`) = 2

If an ID can have only distinct type values, you can do HAVING COUNT(K.TYPE) = 2 instead.

How to use a group by or having clause instead of inner join MYSQL

Sign up