Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL Logic for using LIKE condition based First keyword of a column value with another table

Rama0830Jun 1 2021

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;

image.png

Comments

Post Details

Added on Jun 1 2021
4 comments
1,821 views