Find if multiple values exist in multiple tables

Question

I have an array of words to query. I need to check every column of the 14 tables to see if those words appear in any of the columns in any of the tables in an Oracle database. I then need to select all records that contain any of those words in any of those columns in any of those tables. Can someone provide a high level approach for this? Initially, I tried to search table by table by creating an outer loop to search through the columns and an inner loop to search each column for the words to be queried. However, my intuition tells that Oracle has commands to let me search all columns of all tables for the list of words I need to query. Any advice would be really helpful!!

Thanks!

Same question as:http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle — NoChance, Mar 09 '15 at 20:56
`However, my intuition tells that Oracle has commands to let me search all columns of all tables for the list of words I need to query.` Unfortunately your intuition is not correct. — David Faber, Mar 10 '15 at 01:55

score 0 · Answer 1 · answered Mar 09 '15 at 22:06

Something like the following:

SELECT  t1.FIELD1 ,
        t1.FIELD2 ,
        t1.FIELD3 ,
        t1.FIELD4 ,
        t1.FIELD5 ,
        t1.FIELD6 ,
        t1.FIELD7 ,
        t1.FIELD8 ,
        t1.FIELD9 ,
        t1.FIELD10,
        t2.FIELD1 ,
        t2.FIELD2 ,
        t2.FIELD3 ,
        t2.FIELD4 ,
        t2.FIELD5 ,
        t2.FIELD6 ,
        t2.FIELD7 ,
        t2.FIELD8 ,
        t2.FIELD9 ,
        t2.FIELD10,
        t3.FIELD1 ,
        t3.FIELD2 ,
        t3.FIELD3 ,
        t3.FIELD4 ,
        t3.FIELD5 ,
        t3.FIELD6 ,
        t3.FIELD7 ,
        t3.FIELD8 ,
        t3.FIELD9 ,
        t3.FIELD10,
        t4.FIELD1 ,
        t4.FIELD2 ,
        t4.FIELD3 ,
        t4.FIELD4 ,
        t4.FIELD5 ,
        t4.FIELD6 ,
        t4.FIELD7 ,
        t4.FIELD8 ,
        t4.FIELD9 ,
        t4.FIELD10,
        t5.FIELD1 ,
        t5.FIELD2 ,
        t5.FIELD3 ,
        t5.FIELD4 ,
        t5.FIELD5 ,
        t5.FIELD6 ,
        t5.FIELD7 ,
        t5.FIELD8 ,
        t5.FIELD9 ,
        t5.FIELD10,
        t6.FIELD1 ,
        t6.FIELD2 ,
        t6.FIELD3 ,
        t6.FIELD4 ,
        t6.FIELD5 ,
        t6.FIELD6 ,
        t6.FIELD7 ,
        t6.FIELD8 ,
        t6.FIELD9 ,
        t6.FIELD10,
        t7.FIELD1 ,
        t7.FIELD2 ,
        t7.FIELD3 ,
        t7.FIELD4 ,
        t7.FIELD5 ,
        t7.FIELD6 ,
        t7.FIELD7 ,
        t7.FIELD8 ,
        t7.FIELD9 ,
        t7.FIELD10,
        t8.FIELD1 ,
        t8.FIELD2 ,
        t8.FIELD3 ,
        t8.FIELD4 ,
        t8.FIELD5 ,
        t8.FIELD6 ,
        t8.FIELD7 ,
        t8.FIELD8 ,
        t8.FIELD9 ,
        t8.FIELD10,
        t9.FIELD1 ,
        t9.FIELD2 ,
        t9.FIELD3 ,
        t9.FIELD4 ,
        t9.FIELD5 ,
        t9.FIELD6 ,
        t9.FIELD7 ,
        t9.FIELD8 ,
        t9.FIELD9 ,
        t9.FIELD10,
        t10.FIELD1,
        t10.FIELD2,
        t10.FIELD3,
        t10.FIELD4,
        t10.FIELD5,
        t10.FIELD6,
        t10.FIELD7,
        t10.FIELD8,
        t10.FIELD9,
        t10.FIELD1,
        t11.FIELD1,
        t11.FIELD2,
        t11.FIELD3,
        t11.FIELD4,
        t11.FIELD5,
        t11.FIELD6,
        t11.FIELD7,
        t11.FIELD8,
        t11.FIELD9,
        t11.FIELD1,
        t12.FIELD1,
        t12.FIELD2,
        t12.FIELD3,
        t12.FIELD4,
        t12.FIELD5,
        t12.FIELD6,
        t12.FIELD7,
        t12.FIELD8,
        t12.FIELD9,
        t12.FIELD1,
        t13.FIELD1,
        t13.FIELD2,
        t13.FIELD3,
        t13.FIELD4,
        t13.FIELD5,
        t13.FIELD6,
        t13.FIELD7,
        t13.FIELD8,
        t13.FIELD9,
        t13.FIELD1,
        t14.FIELD1,
        t14.FIELD2,
        t14.FIELD3,
        t14.FIELD4,
        t14.FIELD5,
        t14.FIELD6,
        t14.FIELD7,
        t14.FIELD8,
        t14.FIELD9,
        t14.FIELD1
  FROM TABLE1 t1
       UNION ALL TABLE2 t2
       UNION ALL TABLE3 t3
       UNION ALL TABLE4 t4
       UNION ALL TABLE5 t5
       UNION ALL TABLE6 t6
       UNION ALL TABLE7 t7
       UNION ALL TABLE8 t8
       UNION ALL TABLE9 t9
       UNION ALL TABLE10 t10
       UNION ALL TABLE11 t11
       UNION ALL TABLE12 t12
       UNION ALL TABLE13 t13
       UNION ALL TABLE14 t14
  WHERE t1.FIELD1 LIKE '%whatever%' OR
        t1.FIELD2 LIKE '%whatever%' OR
        t1.FIELD3 LIKE '%whatever%' OR
        t1.FIELD4 LIKE '%whatever%' OR
        t1.FIELD5 LIKE '%whatever%' OR
        t1.FIELD6 LIKE '%whatever%' OR
        t1.FIELD7 LIKE '%whatever%' OR
        t1.FIELD8 LIKE '%whatever%' OR
        t1.FIELD9 LIKE '%whatever%' OR
        t1.FIELD10 LIKE '%whatever%' OR
        t2.FIELD1 LIKE '%whatever%' OR
        t2.FIELD2 LIKE '%whatever%' OR
        t2.FIELD3 LIKE '%whatever%' OR
        t2.FIELD4 LIKE '%whatever%' OR
        t2.FIELD5 LIKE '%whatever%' OR
        t2.FIELD6 LIKE '%whatever%' OR
        t2.FIELD7 LIKE '%whatever%' OR
        t2.FIELD8 LIKE '%whatever%' OR
        t2.FIELD9 LIKE '%whatever%' OR
        t2.FIELD10 LIKE '%whatever%' OR
        t3.FIELD1 LIKE '%whatever%' OR
        t3.FIELD2 LIKE '%whatever%' OR
        t3.FIELD3 LIKE '%whatever%' OR
        t3.FIELD4 LIKE '%whatever%' OR
        t3.FIELD5 LIKE '%whatever%' OR
        t3.FIELD6 LIKE '%whatever%' OR
        t3.FIELD7 LIKE '%whatever%' OR
        t3.FIELD8 LIKE '%whatever%' OR
        t3.FIELD9 LIKE '%whatever%' OR
        t3.FIELD10 LIKE '%whatever%' OR
        t4.FIELD1 LIKE '%whatever%' OR
        t4.FIELD2 LIKE '%whatever%' OR
        t4.FIELD3 LIKE '%whatever%' OR
        t4.FIELD4 LIKE '%whatever%' OR
        t4.FIELD5 LIKE '%whatever%' OR
        t4.FIELD6 LIKE '%whatever%' OR
        t4.FIELD7 LIKE '%whatever%' OR
        t4.FIELD8 LIKE '%whatever%' OR
        t4.FIELD9 LIKE '%whatever%' OR
        t4.FIELD10 LIKE '%whatever%' OR
        t5.FIELD1 LIKE '%whatever%' OR
        t5.FIELD2 LIKE '%whatever%' OR
        t5.FIELD3 LIKE '%whatever%' OR
        t5.FIELD4 LIKE '%whatever%' OR
        t5.FIELD5 LIKE '%whatever%' OR
        t5.FIELD6 LIKE '%whatever%' OR
        t5.FIELD7 LIKE '%whatever%' OR
        t5.FIELD8 LIKE '%whatever%' OR
        t5.FIELD9 LIKE '%whatever%' OR
        t5.FIELD10 LIKE '%whatever%' OR
        t6.FIELD1 LIKE '%whatever%' OR
        t6.FIELD2 LIKE '%whatever%' OR
        t6.FIELD3 LIKE '%whatever%' OR
        t6.FIELD4 LIKE '%whatever%' OR
        t6.FIELD5 LIKE '%whatever%' OR
        t6.FIELD6 LIKE '%whatever%' OR
        t6.FIELD7 LIKE '%whatever%' OR
        t6.FIELD8 LIKE '%whatever%' OR
        t6.FIELD9 LIKE '%whatever%' OR
        t6.FIELD10 LIKE '%whatever%' OR
        t7.FIELD1 LIKE '%whatever%' OR
        t7.FIELD2 LIKE '%whatever%' OR
        t7.FIELD3 LIKE '%whatever%' OR
        t7.FIELD4 LIKE '%whatever%' OR
        t7.FIELD5 LIKE '%whatever%' OR
        t7.FIELD6 LIKE '%whatever%' OR
        t7.FIELD7 LIKE '%whatever%' OR
        t7.FIELD8 LIKE '%whatever%' OR
        t7.FIELD9 LIKE '%whatever%' OR
        t7.FIELD10 LIKE '%whatever%' OR
        t8.FIELD1 LIKE '%whatever%' OR
        t8.FIELD2 LIKE '%whatever%' OR
        t8.FIELD3 LIKE '%whatever%' OR
        t8.FIELD4 LIKE '%whatever%' OR
        t8.FIELD5 LIKE '%whatever%' OR
        t8.FIELD6 LIKE '%whatever%' OR
        t8.FIELD7 LIKE '%whatever%' OR
        t8.FIELD8 LIKE '%whatever%' OR
        t8.FIELD9 LIKE '%whatever%' OR
        t8.FIELD10 LIKE '%whatever%' OR
        t9.FIELD1 LIKE '%whatever%' OR
        t9.FIELD2 LIKE '%whatever%' OR
        t9.FIELD3 LIKE '%whatever%' OR
        t9.FIELD4 LIKE '%whatever%' OR
        t9.FIELD5 LIKE '%whatever%' OR
        t9.FIELD6 LIKE '%whatever%' OR
        t9.FIELD7 LIKE '%whatever%' OR
        t9.FIELD8 LIKE '%whatever%' OR
        t9.FIELD9 LIKE '%whatever%' OR
        t9.FIELD10 LIKE '%whatever%' OR
        t10.FIELD1 LIKE '%whatever%' OR
        t10.FIELD2 LIKE '%whatever%' OR
        t10.FIELD3 LIKE '%whatever%' OR
        t10.FIELD4 LIKE '%whatever%' OR
        t10.FIELD5 LIKE '%whatever%' OR
        t10.FIELD6 LIKE '%whatever%' OR
        t10.FIELD7 LIKE '%whatever%' OR
        t10.FIELD8 LIKE '%whatever%' OR
        t10.FIELD9 LIKE '%whatever%' OR
        t10.FIELD10 LIKE '%whatever%' OR
        t11.FIELD1 LIKE '%whatever%' OR
        t11.FIELD2 LIKE '%whatever%' OR
        t11.FIELD3 LIKE '%whatever%' OR
        t11.FIELD4 LIKE '%whatever%' OR
        t11.FIELD5 LIKE '%whatever%' OR
        t11.FIELD6 LIKE '%whatever%' OR
        t11.FIELD7 LIKE '%whatever%' OR
        t11.FIELD8 LIKE '%whatever%' OR
        t11.FIELD9 LIKE '%whatever%' OR
        t11.FIELD10 LIKE '%whatever%' OR
        t12.FIELD1 LIKE '%whatever%' OR
        t12.FIELD2 LIKE '%whatever%' OR
        t12.FIELD3 LIKE '%whatever%' OR
        t12.FIELD4 LIKE '%whatever%' OR
        t12.FIELD5 LIKE '%whatever%' OR
        t12.FIELD6 LIKE '%whatever%' OR
        t12.FIELD7 LIKE '%whatever%' OR
        t12.FIELD8 LIKE '%whatever%' OR
        t12.FIELD9 LIKE '%whatever%' OR
        t12.FIELD10 LIKE '%whatever%' OR
        t13.FIELD1 LIKE '%whatever%' OR
        t13.FIELD2 LIKE '%whatever%' OR
        t13.FIELD3 LIKE '%whatever%' OR
        t13.FIELD4 LIKE '%whatever%' OR
        t13.FIELD5 LIKE '%whatever%' OR
        t13.FIELD6 LIKE '%whatever%' OR
        t13.FIELD7 LIKE '%whatever%' OR
        t13.FIELD8 LIKE '%whatever%' OR
        t13.FIELD9 LIKE '%whatever%' OR
        t13.FIELD10 LIKE '%whatever%' OR
        t14.FIELD1 LIKE '%whatever%' OR
        t14.FIELD2 LIKE '%whatever%' OR
        t14.FIELD3 LIKE '%whatever%' OR
        t14.FIELD4 LIKE '%whatever%' OR
        t14.FIELD5 LIKE '%whatever%' OR
        t14.FIELD6 LIKE '%whatever%' OR
        t14.FIELD7 LIKE '%whatever%' OR
        t14.FIELD8 LIKE '%whatever%' OR
        t14.FIELD9 LIKE '%whatever%' OR
        t14.FIELD10 LIKE '%whatever%';

Hopefully by now you recognize that this is a really lousy thing to have to do. I suspect that the tables you're working with could use a little bit of normalization, and suggest that you might consider putting in a bit of effort on doing so. When properly done a relational database is not just a collection of flat files. Not knowing any details it's more or less impossible to offer any more detailed advice.

Best of luck.

Find if multiple values exist in multiple tables

1 Answers1