yankel yankel - 4 years ago 81
MySQL Question

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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download