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

Rama0830

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;

Rama0830

Based on some help in my previous post, I have used the below SQL. But not able to figure out logic when I have multiple keywords and then picking the first keyword.

https://community.oracle.com/tech/developers/discussion/4484475/sql-logic-for-using-like-condition-based-on-column-values-of-another-table

SELECT   *
FROM     master_table    m
JOIN     reference_table r  ON LOWER (m.location) LIKE '%' || LOWER (r.type) || '%'
ORDER BY m.name, r.type

-Rama

Frank Kulash

Hi, @Rama0830

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.

...

INSERT INTO MASTER_TABLE VALUES ('John','005 3rd Floor Hallway');

INSERT INTO MASTER_TABLE VALUES ('David','Hallway utility');

Okay, for 'David', the first word in the location column is 'Hallway', so you want to ignore 'utility' when matching 'David'.

For 'John', the first word in the location column is '005'. Does that mean you want to ignore '3rd', 'Floor' and 'Hallway'?

Frank Kulash

Hi, @Rama0830

Do you mean when two or more rows from reference_table match the same row from master_table, then you only want the one that matched earlier in location? For example, for 'John', location = 'Hallway utility'. This matches two rows in reference_table

1. type = 'Hallway' (found at position 1 in 'Hallway utility', and
2. type = 'utility' (found at position 9 of 'Hallway utility'

So, in this case, you only want the result set to include the match from position 1. Is that right?

Here's one way to do that:

WITH  all_matches  AS
(
  SELECT  m.*, r.*
  ,	  ROW_NUMBER () OVER ( PARTITION BY m.name
			       ORDER BY	     INSTR ( LOWER (m.location)
  	  		   	  	 	   , LOWER (r.type)
					 	   )
  	  	    	      ) AS rn
  FROM  master_table   m
  JOIN  reference_table r ON INSTR ( LOWER (m.location)
  	  		   	   , LOWER (r.type)
				   ) > 0
)
SELECT   name, location, category, type
FROM	 all_matches
WHERE	 rn = 1
;

Notice this uses INSTR instead of LIKE. Whenever you're tempted to use LIKE, also consider INSTR; sometimes it can do the same job, and do it more efficiently.

User_H3J7U

select *
from master_table mt
outer apply (
 select type, category
 from reference_table rt
 where    instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ') > 0
 order by instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ')
 fetch first 1 row only
);

NAME       LOCATION                TYPE       CATEGORY                       
John       005 3rd Floor Hallway   Hallway    Hallway, Stairs, Lobby          
David      Hallway utility         Hallway    Hallway, Stairs, Lobby          
Ashok      playroom                playroom   Community                       
Kyle       playroom bathroom       playroom   Community                       
Srikanth   Outdoor activity        outdoor    Exterior                        
Pavan      Utility Outdoor         utility    Basement, mechanical, storage   
Mike       Other                   other      Other