SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(8,'/',9) AS p_d,(8/9)*100 AS percentage FROM `our_price_lists`
INNER JOIN mappings ON
CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3),' ',SPLIT_STRING(mappings.name, ' ', 4),' ',SPLIT_STRING(mappings.name, ' ', 5),' ',SPLIT_STRING(mappings.name, ' ', 6),' ',SPLIT_STRING(mappings.name, ' ', 7),' ',SPLIT_STRING(mappings.name, ' ', 8))
=
CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 4),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 5),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 6),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 7),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 8))


AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(7,'/',9) AS p_d,(7/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3),' ',SPLIT_STRING(mappings.name, ' ', 4),' ',SPLIT_STRING(mappings.name, ' ', 5),' ',SPLIT_STRING(mappings.name, ' ', 6),' ',SPLIT_STRING(mappings.name, ' ', 7))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 4),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 5),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 6),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 7)) AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(6,'/',9) AS p_d,(6/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3),' ',SPLIT_STRING(mappings.name, ' ', 4),' ',SPLIT_STRING(mappings.name, ' ', 5),' ',SPLIT_STRING(mappings.name, ' ', 6))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 4),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 5),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 6)) AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(5,'/',9) AS p_d,(5/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3),' ',SPLIT_STRING(mappings.name, ' ', 4),' ',SPLIT_STRING(mappings.name, ' ', 5))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 4),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 5)) AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(4,'/',9) AS p_d,(4/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3),' ',SPLIT_STRING(mappings.name, ' ', 4))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 4)) AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(3,'/',9) AS p_d,(3/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2),' ',SPLIT_STRING(mappings.name, ' ', 3))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3)) AND our_price_lists.service_name_ar!=mappings.name UNION SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(2,'/',9) AS p_d,(2/9)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON CONCAT(SPLIT_STRING(mappings.name, ' ', 1),' ',SPLIT_STRING(mappings.name, ' ', 2))=CONCAT(SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1),' ',SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2)) AND our_price_lists.service_name_ar!=mappings.name UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 1)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 1)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 1)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 2)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 2)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 2)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 3)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 3)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 3)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 4)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 4)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 4)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 5)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 5)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 5)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 6)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 6)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 6)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 7)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 7)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 7)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 8)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 8)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 8)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 9)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 1) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 9)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 2) AND our_price_lists.service_name_ar!=mappings.name  UNION  SELECT our_price_lists.id,our_price_lists.`service_name_ar`,mappings.name,mappings.id AS mapping_id,mappings.code AS mapping_code,CONCAT(1,'/',3) AS p_d,(1/3)*100 AS percentage FROM `our_price_lists` INNER JOIN mappings ON SPLIT_STRING(mappings.name, ' ', 9)=SPLIT_STRING(our_price_lists.service_name_ar, ' ', 3) AND our_price_lists.service_name_ar!=mappings.name









SELECT our_price_list_id,mapping_id,percentage FROM (
    SELECT our_price_lists_service_name_ar_explodes.our_price_list_id,mappings_name_explodes.mapping_id,
    (COUNT('x')/MAX(our_price_lists_service_name_ar_explodes.position)*100) AS percentage,

     ROW_NUMBER() OVER (PARTITION BY our_price_list_id ORDER BY percentage DESC) Rn FROM our_price_lists_service_name_ar_explodes
INNER JOIN mappings_name_explodes ON mappings_name_explodes.string=our_price_lists_service_name_ar_explodes.string
WHERE Rn=1
GROUP BY our_price_lists_service_name_ar_explodes.our_price_list_id,mappings_name_explodes.mapping_id

) AS dd





INSERT INTO our_price_list_mapping_matches (`our_price_list_id`, `mapping_id`, `percentage`) SELECT our_price_list_id,mapping_id,percentage FROM (
    SELECT our_price_lists_service_name_ar_explodes.our_price_list_id,mappings_name_explodes.mapping_id,(COUNT('x')/MAX(our_price_lists_service_name_ar_explodes.position)*100) AS percentage FROM our_price_lists_service_name_ar_explodes
INNER JOIN mappings_name_explodes ON mappings_name_explodes.string=our_price_lists_service_name_ar_explodes.string
GROUP BY our_price_lists_service_name_ar_explodes.our_price_list_id,mappings_name_explodes.mapping_id

) AS dd




SELECT CONCAT("DELETE FROM our_price_list_mapping_matches WHERE our_price_list_id='",our_price_list_id,"' AND percentage!='",MAX(percentage),"';") FROM `our_price_list_mapping_matches`  GROUP BY our_price_list_id
