Forum Stats

  • 3,768,301 Users
  • 2,252,772 Discussions
  • 7,874,520 Comments

Discussions

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

Rama0830
Rama0830 Member Posts: 27 Red Ribbon

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;



Answers

  • Rama0830
    Rama0830 Member Posts: 27 Red Ribbon

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    edited Jun 2, 2021 1:01AM

    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
    User_H3J7U Member Posts: 640 Silver Trophy
    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