Keywords by Doc Type SQL Query

[Laura_Wilke]

I am trying to write a SQL query that will return all doc types with their associated keywords. I found the doctype table and various key tables but I’m not sure how they relate. Can this be done?

[Bill_Schoby · Accepted solution]

SELECT dt.itemtypename, kt.keytype,
     case datatype
         when 1 then 'Num 20'
         when 2 then 'AN Dual ' + CONVERT(varchar(5), keytypelen)
         when 3 then 'Currency'
         when 4 then 'Date'
         when 6 then 'Num 9'
         when 10 then 'AN Single ' + CONVERT(varchar(5), keytypelen)
         when 13 then 'AN ' + CONVERT(varchar(5), keytypelen)
         else 'other'
     end as kwDataType
     , itg.itemtypegroupname
     , kt.keywordsetnum
FROM hsi.doctype dt
     inner join hsi.itemtypexkeyword itk on dt.itemtypenum = itk.itemtypenum
     inner join hsi.keytypetable kt on itk.keytypenum = kt.keytypenum
     inner join hsi.itemtypegroup itg on dt.itemtypegroupnum = itg.itemtypegroupnum
order by 1, 2

[Laura_Wilke]

This is perfect. Thank you for the quick response!