Forum Stats

  • 3,769,310 Users
  • 2,252,947 Discussions
  • 7,874,983 Comments

Discussions

SQL Logic for using LIKE condition based on column values of another table

Rama0830
Rama0830 Member Posts: 27 Red Ribbon
edited May 16, 2021 4:52AM in SQL & PL/SQL

We have a requirement where there are two tables with no keys between them (Master Table and Reference Table)

I have attached the script and also the excel spreadsheet with the expected results.

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.

For example (from the attachment):-

For John, in the location column, there is Hallway, so it should check-in from the Type column in the Reference table and give output as Hallway, Stairs, Lobby.

I tried different methods with LIKE condition but failed.

Please do suggest if this is achievable or not? and also please do suggest any recommendations.

Thank you so much for your help.

-Rama


Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    Accepted Answer

    Hi, @Rama0830

    Thanks for posting the sample data and results. Wouldn't it be better to test with some cases where a master row matched more than one reference row, or didn't match any, like this?

    INSERT INTO MASTER_TABLE (name, location) VALUES ('Rama0830', 'Displayroom Hallway');
    INSERT INTO MASTER_TABLE (name, location) VALUES ('Fubar', 'Parking Lot');
    

    The Solution Solomon posted about 45 hours ago almost does what you now requested. It assumes you're looking for an exact match (e.g. 'Recptionist' is different from 'Reception') and that the matches are case-sensitive. Since you want 'Receptionist' to match 'reception', you can modify Solomon's solution like this:

    SELECT   *
    FROM     master_table    m
    JOIN     reference_table r  ON LOWER (m.location) LIKE '%' || LOWER (r.type) || '%'
    ORDER BY m.name, r.type   -- or whatever you want
    /
    

    In this case, you could also use INSTR instead of LIKE.

    Rama0830
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @Rama0830

    I have attached the script and also the excel spreadsheet with the expected results.

    Post everything right in this space. Not everyone who wants to help you can or will open attachments. Always give your full Oracle database version (e.g. 12.2.0.1.0).

    I tried different methods with LIKE condition but failed.

    If it's worth mentioning this, then it's worth posting what you tried, at least what you think was your best attempt. It can help explain what you need to do.

  • User_XTXRU
    User_XTXRU Member Posts: 3 Blue Ribbon

    Hi, you can use XMLAGG function. You can create a function that returns aggregated string or you can use as as a sub query.


    CREATE OR REPLACE FUNCTION SAMPLE_XML_AGG (P_SEARCH_KEY IN VARCHAR2) RETURN VARCHAR2 IS

    P_DUMMY VARCHAR2(4000);

    BEGIN

      SELECT SUBSTR ( REPLACE ( REPLACE (XMLAGG (XMLELEMENT ("x", TMP.DETAIL_TABLE_COLUMN)), '</x>'), '<x>', ','), 2, 4000) INTO P_DUMMY

       FROM ( SELECT L.DETAIL_TABLE_COLUMN FROM DETAIL_TABLE L

           WHERE (L.REF_COLUMN = P_SEARCH_KEY)

          GROUP BY L.REF_COLUMN

         ) TMP;


      RETURN P_DUMMY;

    END;

    Rama0830
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond
    select  *
      from  master_table m,
            reference_table r
      where ' ' || m.location || ' ' like '% ' || r.type || ' %'
    /
    
    NAME       LOCATION                       CATEGORY                       TYPE
    ---------- ------------------------------ ------------------------------ ----------
    Pavan      private utility                Basement, mechanical, storage  utility
    Mike       utility room                   Basement, mechanical, storage  utility
    John       005 3rd Floor Hallway          Hallway, Stairs, Lobby         Hallway
    David      11 floor Hallway               Hallway, Stairs, Lobby         Hallway
    
    
    SQL>
    

    SY.

    Rama0830
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond
    edited May 16, 2021 1:32PM

    Question is if words in location are separated by spaces and aren't followed by punctuation (or other non-space characters). Then such characters must be replaced by spaces first:

    SQL> insert
      2    into master_table
      3    values('Jim','3rd Floor Hallway, Building 9')
      4  /
    
    1 row created.
    
    SQL> select  *
      2    from  master_table m,
      3          reference_table r
      4    where ' ' || m.location || ' ' like '% ' || r.type || ' %'
      5  /
    
    NAME       LOCATION                                 CATEGORY                       TYPE
    ---------- ---------------------------------------- ------------------------------ ----------
    Pavan      private utility                          Basement, mechanical, storage  utility
    Mike       utility room                             Basement, mechanical, storage  utility
    John       005 3rd Floor Hallway                    Hallway, Stairs, Lobby         Hallway
    David      11 floor Hallway                         Hallway, Stairs, Lobby         Hallway
    
    SQL> select  *
      2    from  master_table m,
      3          reference_table r
      4    where ' ' || regexp_replace(m.location,'\W',' ') || ' ' like '% ' || r.type || ' %'
      5  /
    
    NAME       LOCATION                                 CATEGORY                       TYPE
    ---------- ---------------------------------------- ------------------------------ ----------
    Pavan      private utility                          Basement, mechanical, storage  utility
    Mike       utility room                             Basement, mechanical, storage  utility
    John       005 3rd Floor Hallway                    Hallway, Stairs, Lobby         Hallway
    David      11 floor Hallway                         Hallway, Stairs, Lobby         Hallway
    Jim        3rd Floor Hallway, Building 9            Hallway, Stairs, Lobby         Hallway
    
    SQL>
    

    SY.

    Rama0830
  • Rama0830
    Rama0830 Member Posts: 27 Red Ribbon
  • Rama0830
    Rama0830 Member Posts: 27 Red Ribbon

    Thank you all for your responses. It did help..

    I should have been more careful with my question if the result can be achieved from just using CASE statement?

    Ex:-

    select  m.location, 
    case when ' ' || m.location || ' ' like '% ' ||(select distinct r.type from reference_table r)|| ' %'
    then r.category else 'NA' end Category
    from  master_table m
    

    The above query won't work. Can you please help me with this step?

    Thank you,

    Rama

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond


    Why wait until next time? What's wrong with this time?

    Obviously your question is not answered yet. I might be interested in helping you, but I am not going to open attachments from an unknown source. If others aren't concerned about the risks, that's fine, but I am sure you would have had more interest in your question (from more forum participants) if you had the data posted here, rather than through attachments.

    Rama0830
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    edited May 17, 2021 10:05PM

    Hi, @Rama0830

    I should have been more careful with my question if the result can be achieved from just using CASE statement?

    Ex:-

    select  m.location, 
    case when ' ' || m.location || ' ' like '% ' ||(select distinct r.type from reference_table r)|| ' %'
    then r.category else 'NA' end Category
    from  master_table m
    

    The above query won't work.

    That's right; a scalar sub-query, such as (select distinct r.type from reference_table r) , will raise an error if it produces more than one row.

    By the way, what you posted is a CASE expression, not a CASE statement. A CASE statement is something different.

    Can you please help me with this step?

    Can you post the sample data and desired results right in this space, and not in an attachment?

    Rama0830
  • Rama0830
    Rama0830 Member Posts: 27 Red Ribbon


    Hi,


    Here you go..


    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','11 floor Hallway');

    INSERT INTO MASTER_TABLE

    VALUES ('Ashok','Receptionist Desk');

    INSERT INTO MASTER_TABLE

    VALUES ('Kyle','BATHROOM 1 (A/B)');

    INSERT INTO MASTER_TABLE

    VALUES ('Srikanth','Health Club Child Playroom');

    INSERT INTO MASTER_TABLE

    VALUES ('Pavan','private utility');

    INSERT INTO MASTER_TABLE

    VALUES ('Mike','utility room');


    CREATE TABLE REFERENCE_TABLE

    (CATEGORY VARCHAR2(100),

    TYPE VARCHAR2(100));


    INSERT INTO REFERENCE_TABLE

    VALUES ('Basement, mechanical, storage','utility');

    INSERT INTO REFERENCE_TABLE

    VALUES ('Community','playroom');

    INSERT INTO REFERENCE_TABLE

    VALUES ('Community','bathroom');

    INSERT INTO REFERENCE_TABLE

    VALUES ('Hallway, Stairs, Lobby','Hallway');

    INSERT INTO REFERENCE_TABLE

    VALUES ('Hallway, Stairs, Lobby','reception');


    select * from REFERENCE_TABLE;

    select * from MASTER_TABLE;


    Thank you for your time and help.

  • Rama0830
    Rama0830 Member Posts: 27 Red Ribbon

    I have provided the script in the above comment.

    Below is the expected results.


    Thank you,