How can I perform a conditional join based on wildcard match in mysql?-CodePudding

I have two tables of values, the first one contains (among other things) product model numbers while the second contains information about warranties. I don't have an exhaustive list of all the products (in part because new products will be released that I won't be aware of, but I need the reported warranty to be accurate), so the second table contains values for a wildcard match, e.g. something like '1080', 'HD', '4K', etc. What I know is that products that have particular model numbers have specific warranties associated with them, i.e. 1080, or HD products have a 3 year warranty, while the 4K (and others) are 5 (for example).

My current non-scalable solution is to use a CASE statement, but this requires a separate line item for the case statement for every know model wildcard match; which means that ETL will become out of date, potentially rapidly - which is problematic.

CREATE TABLE sales AS
MODEL varchar(255), sold_date as DATETIME;

INSERT INTO sales (model, sold_date)
VALUES ('PANASONIC_HD_ANDROID', '2020-01-01');

INSERT INTO sales (model, sold_date)
VALUES ('PANASONIC_1080_ANDROID', '2020-01-01');

INSERT INTO sales (model, sold_date)
VALUES ('SONY_HD_ROKU', '2020-01-01');


INSERT INTO sales (model, sold_date)
VALUES ('SONY_1080_ROKU', '2020-01-01');


INSERT INTO sales (model, sold_date)
VALUES ('SONY_4K_ROKU', '2020-01-01');

CREATE TABLE warranties AS
model_wildcard VARCHAR(255),
warranty_length INT;

INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('HD', 3);
INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('4K', 5);
INSERT INTO warranties (model_wildcard, warranty_length) VALUES ('1080', 3);

What I'm looking to accomplish is something like this

SELECT sales.model, sales.sold_date, DATE_ADD(sales.sold_date, INTERVAL S.warranty_length)
FROM sales as S
LEFT JOIN warranties W on S.model LIKE W.model_wildcard

which obviously won't work, but I think that gets across the idea of what I'm trying to do.

CodePudding user response：

SELECT *,
       ss.sold_date   INTERVAL ww.warranty_length YEAR warranty_till
FROM sales ss
LEFT JOIN warranties ww ON LOCATE(ww.model_wildcard, ss.model);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5b8777058c71635e1b082fddf8eb902b

You must understand that it is possible that the model name does not match any pattern (LEFT JOIN is used, NULL will be returned in according columns). Or, backward, it may match more than one pattern (all matchings will be returned).

PS. ON INSTR(ss.model, ww.model_wildcard) can be used also.