We have a requirement where there are two tables with no keys between them (Master Table and Reference Table)
The Master table has Name and Location columns, Reference table has Category and Type columns.
Master table Location column should check values in Reference table Type column and then give the relevant Category from the Reference table. This issue has been solved in other post
SQL Logic for using LIKE condition based on column values of another table
View Post
But here I have a complex scenario, if there are multiple keywords under the Location column then we should pick only the first keyword and should map it to the Reference table Type column.
For example (from the screenshot below):-
For David, the value in the Location column is **Hallway utility (**two keywords wrt Reference table). So it should first check the first keyword which is Hallway and then map it to the Reference table to give output as Hallway, Stairs, Lobby.
Please do suggest if this is achievable or not? and also please do suggest any recommendations.
Thank you so much for your help.
-Rama
CREATE TABLE MASTER_TABLE(NAME VARCHAR2(100), LOCATION VARCHAR2(100));
INSERT INTO MASTER_TABLE VALUES ('John','005 3rd Floor Hallway');
INSERT INTO MASTER_TABLE VALUES ('David','Hallway utility');
INSERT INTO MASTER_TABLE VALUES ('Ashok','playroom');
INSERT INTO MASTER_TABLE VALUES ('Kyle','playroom bathroom');
INSERT INTO MASTER_TABLE VALUES ('Srikanth','Outdoor activity');
INSERT INTO MASTER_TABLE VALUES ('Pavan','Utility Outdoor');
INSERT INTO MASTER_TABLE VALUES ('Mike','Other');
CREATE TABLE REFERENCE_TABLE (CATEGORY VARCHAR2(100), TYPE VARCHAR2(100));
INSERT INTO REFERENCE_TABLE VALUES ('Hallway, Stairs, Lobby','Hallway');
INSERT INTO REFERENCE_TABLE VALUES ('Basement, mechanical, storage','utility');
INSERT INTO REFERENCE_TABLE VALUES ('Community','playroom');
INSERT INTO REFERENCE_TABLE VALUES ('office','bathroom');
INSERT INTO REFERENCE_TABLE VALUES ('Other','other');
select * from REFERENCE_TABLE;
select * from MASTER_TABLE;
