SQL query to find if multiple instances of a keyword exist

[Timothy_Shepler]

I am looking for a SQL query to search for if multiple instances of a keyword exist.

[Brad_Phillips · Accepted solution]

Tim,

Here's an example of me accomplishing this on my test system.  You will need to change the names of the table and columns, but the logic is the same:

select keyvaluesmall, count(*)
From hsi.keyitem102
group by keyvaluesmall
having count(*) >= 2

[Jeff_Evelend]

I've used this SQL statement before.  Hopefully it helps:

select itemname,

 COUNT(itemname) AS NumOccurrences

FROM hsi.itemdata

GROUP BY itemname

HAVING ( COUNT(itemname) > 1 )

[Scott_Hirsch · Accepted solution]

If you are using MIKGs, and you want to check for MIKG instances, you will need to check the hsi.keyrecorddata### table.  Here is an example I did in my test system for a Document Type with a MIKG on it to find if there was multiple instances of a MIKG on it (basically Multiple Instances of a KW assuming all keywords are filled in on the MIKG):

select COUNT(itemnum) AS "# of MIKG Instances", itemnum
from hsi.keyrecorddata134
group by itemnum
having COUNT(itemnum) >1

This would give you a result of itemnums that have more than one instance of that particular MIKG, and how many instances there are.

* One thing to remember is that if you are using MIKG with a particular keyword, the Settings do not allow you to also have the same Keyword as a loose keyword.

Are you looking for MIKGs or Loose Keywords?

[Scott_Hirsch]

Hey Tim!

Did any of these help get what you needed?