Oracle Finding a string match from multiple database tables

Question

This is somewhat a complex problem to describe, but I'll try to explain it with an example. I thought I would have been able to use the Oracle Instr function to accomplish this, but it does not accept queries as parameters.

Here is a simplification of my data:

  Table1
   Person         Qualities
   Joe            5,6,7,8,9
   Mary           7,8,10,15,20
   Bob            7,8,9,10,11,12

   Table2
   Id             Desc
   5              Nice
   6              Tall
   7              Short

   Table3
   Id             Desc
   8              Angry
   9              Sad
   10             Fun

   Table4
   Id             Desc
   11             Boring    
   12             Happy
   15             Cool
   20             Mad

Here is somewhat of a query to give an idea of what I'm trying to accomplish:

   select * from table1 
   where instr (Qualities, select Id from table2, 1,1) <> 0
   and instr (Qualities, select Id from table3, 1,1) <> 0 
   and instr (Qualities, select Id from table3, 1,1) <> 0

I'm trying to figure out which people have at least 1 quality from each of the 3 groups of qualities (tables 2,3, and 4)

So Joe would not be returned in the results because he does not have the quality from each of the 3 groups, but Mary and Joe would since they have at least 1 quality from each group.

We are running Oracle 12, thanks!

Fix your data model! Don't store numbers in strings! Don't store multiple values in a string column! — Gordon Linoff
– Gordon Linoff, Commented Nov 8, 2019 at 16:12
Gordon, There's a long story behind that which is beyond the scope of this post. — Carl
– Carl, Commented Nov 8, 2019 at 17:39

Littlefoot · Accepted Answer · 2019-11-08 17:01:52Z

Here's one option:

SQL> with
  2  table1 (person, qualities) as
  3    (select 'Joe', '5,6,7,8,9' from dual union all
  4     select 'Mary', '7,8,10,15,20' from dual union all
  5     select 'Bob', '7,8,9,10,11,12' from dual
  6    ),
  7  table2 (id, descr) as
  8    (select 5, 'Nice' from dual union all
  9     select 6, 'Tall' from dual union all
 10     select 7, 'Short' from dual
 11    ),
 12  table3 (id, descr) as
 13    (select 8, 'Angry' from dual union all
 14     select 9, 'Sad' from dual union all
 15     select 10, 'Fun' from dual
 16    ),
 17  table4 (id, descr) as
 18    (select 11, 'Boring' from dual union all
 19     select 12, 'Happy' from dual union all
 20     select 15, 'Cool' from dual union all
 21     select 20, 'Mad' from dual
 22    ),
 23  t1new (person, id) as
 24    (select person, regexp_substr(qualities, '[^,]+', 1, column_value) id
 25     from table1 cross join table(cast(multiset(select level from dual
 26                                                connect by level <= regexp_count(qualities, ',') + 1
 27                                               ) as sys.odcinumberlist))
 28    )
 29  select a.person,
 30        count(b.id) bid,
 31        count(c.id) cid,
 32        count(d.id) did
 33  from t1new a left join table2 b on a.id = b.id
 34               left join table3 c on a.id = c.id
 35               left join table4 d on a.id = d.id
 36  group by a.person
 37  having (    count(b.id) > 0
 38          and count(c.id) > 0
 39          and count(d.id) > 0
 40         );

PERS        BID        CID        DID
---- ---------- ---------- ----------
Bob           1          3          2
Mary          1          2          2

SQL>

What does it do?

lines #1 - 22 represent your sample data
T1NEW CTE (lines #23 - 28) splits comma-separated qualities into rows, per every person
final select (lines #29 - 40) are outer joining t1new with each of "description" tables (table2/3/4) and counting how many qualities are contained in there for each of person's qualities (represented by rows from t1new)
having clause is here to return only desired persons; each of those counts have to be a positive number

stefan · Accepted Answer · 2019-11-08 21:10:48Z

Maybe this will help: {1} Create a view that categorises all qualities and allows you to SELECT quality IDs and categories . {2} JOIN the view to TABLE1 and use a join condition that "splits" the CSV value stored in TABLE1.

{1} View

create or replace view allqualities
as
select 1 as category, id as qid, descr from table2
union
select 2, id, descr from table3
union
select 3, id, descr from table4
;

select * from allqualities order by category, qid ;

  CATEGORY        QID DESCR 
---------- ---------- ------
         1          5 Nice  
         1          6 Tall  
         1          7 Short 
         2          8 Angry 
         2          9 Sad   
         2         10 Fun   
         3         11 Boring
         3         12 Happy 
         3         15 Cool  
         3         20 Mad

{2} Query

--  JOIN CONDITION:
--  {1} add a comma at the start and at the end of T1.qualities
--  {2} remove all blanks (spaces) from T1.qualities
--  {3} use LIKE and the qid (of allqualities), wrapped in commas
--
--  inline view: use UNIQUE, otherwise we may get counts > 3
--

select person
from (
  select unique person, category
  from table1 T1 
    join allqualities A 
      on ',' || replace( T1.qualities, ' ', '' ) || ',' like '%,' || A.qid || ',%'
)
group by person
having count(*) = ( select count( distinct category ) from allqualities )
;

-- result
PERSON   
Bob      
Mary

Tested w/ Oracle 18c and 11g. DBfiddle here.

Collectives™ on Stack Overflow

Oracle Finding a string match from multiple database tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related