0





12

Advertisement

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

Question author Yankel | Source

Advertisement


0


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.

Answer author Jarlh