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

Rama0830

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

Scripts.txt

Results (1).xlsx

Frank Kulash

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.

Frank Kulash

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

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;

Solomon Yakobson

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.

Solomon Yakobson

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

https://community.oracle.com/tech/developers/discussion/comment/16795622#Comment_16795622

Sure next time, I will make sure to put everything in the space.

Rama0830

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

https://community.oracle.com/tech/developers/discussion/comment/16795762#Comment_16795762

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.

Frank Kulash

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

https://community.oracle.com/tech/developers/discussion/comment/16795765#Comment_16795765

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

https://community.oracle.com/tech/developers/discussion/comment/16795768#Comment_16795768

I have provided the script in the above comment.

Below is the expected results.

Thank you,