I have a table of incidents that have a short_description. I'm trying to assign them to a category based on the text in that short_description (not ideal, I know, but I'm working with an existing system and don't have much control). So I created a lookup table with search_text to look for, and the category value that should be assigned to the incident. In some cases more than one search_text value matches the short_description. I want to use the priority field to choose the highest priority (lowest priority value, such as 1) when this happens. I feel like maybe this involves a window function or something, but I'm not sure how to approach it.
Can someone help me with the changes needed in the logic below?
CREATE TABLE snow_incident_s_2 (
SNUMBER varchar(40) DEFAULT NULL,
SHORT_DESCRIPTION varchar(200) DEFAULT NULL
INSERT INTO snow_incident_s_2 (snumber, short_description) values ('INC15535802','Prognosis::ADMINCLUSTER ::CmaDiskPartitionNearFull, PROGNOSIS:ADMINCLUSTER');
CREATE TABLE lkp_incident_category_2 (
incident_category_id smallint(6) NOT NULL AUTO_INCREMENT,
incident_type varchar(10) DEFAULT NULL,
category varchar(100) NOT NULL,
search_text varchar(200) NOT NULL,
priority smallint(6) DEFAULT NULL,
PRIMARY KEY (incident_category_id)
INSERT INTO lkp_incident_category_2 (incident_type, category, search_text, priority) values ('INC','Cluster','Cluster',1);
INSERT INTO lkp_incident_category_2 (incident_type, category, search_text, priority) values ('INC','Disk','Disk',2);
lkp_incident_category_2 ic ON inc.short_description LIKE CONCAT('%', ic.search_text, '%')
AND ic.incident_type = 'INC'
window functions don't exist in mysql. but you can mimic them using variables.
I have been trying to test this but sqlfiddle is not cooperating so here is what I believe should work for you give it a try.
SELECT * FROM ( SELECT inc.snumber ,inc.short_description ,ic.search_text ,ic.category ,(@rownum := IF(@incnum=inc.snumber,@rownum+1,1)) as IncidentRowNum ,@incnum := inc.snumber FROM snow_incident_s_2 inc LEFT JOIN lkp_incident_category_2 ic ON inc.short_description LIKE CONCAT('%', ic.search_text, '%') AND ic.incident_type = 'INC' CROSS JOIN (SELECT @rownum := 0, @incnum := '') var ORDER BY ic.priority ) t WHERE t.IncidentRowNum = 1 ;
Fully tested and functional here is a sqlfiddle of it http://sqlfiddle.com/#!9/75ff1/7