Q
Problem solve Get help with specific problems with your technologies, process and projects.

Searching data in more than one table

Is it possible to search data in more than one table? I have more than 110 tables in a database (e.g. db1). I have one column (i.e. city) in more than one table. Is it possible to search all city fields, "Los Angeles," where they exist in the database's different tables?
I am going to assume that you want to get every row in every table that has the column CITY in it with a value of "Los Angeles." I am also assuming that you do not care about whether there is any other relationship that needs to be satisfied. This type of requirement is prevalent in a data warehouse scenario and one way to do it is through what we call a Cartesian product. However, you may also want to individually query each table. Now, if you do not know which tables have the column CITY in it, then you can use the data dictionary view USER_TAB_COLUMNS. You can also dynamically create your query as follows using this data dictionary view.
SELECT TABLE_NAME 
  FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'CITY'

You can dynamically create the query that will retrieve the rows from all the tables having a column named CITY with a value of Los Angeles using two queries. The first query will create the SELECT * CLAUSE and second query will create the where clause. Combining the result sets using an editor you can get your final SQL statement.

Let's say that there are five tables in your database with column CITY with the following names:
TABLE_1
TABLE_2
TABLE_3
TABLE_4
TABLE_5

In the queries below I have used the function CHR to print the single quote whose ASCII value is 39. I need this quote as part of my result. The Quotes around Los Angeles is to treat Los Angeles as string literal for the concatenation purpose below. I also used the UPPER built-in function. You may or may not need to use it.

Query 1:

Set heading off
Set pagesize 200

SELECT DECODE(rownum, 1,
'SELECT * from ' || table_name || ' T'|| rownum,
',' || table_name || ' T'|| rownum) col_val
FROM user_tab_columns where column_name = 'CITY'
ORDER BY ROWNUM

This query will result in the following output

SELECT * from TABLE_1 T1
,TABLE_2 T2
,TABLE_3 T3
,TABLE_4 T4
,TABLE_5 T5

Query 2:

SELECT DECODE(rownum, 1, 'WHERE UPPER(T'|| rownum || '.' || 'CITY) = ' || CHR
(39) || 'LOS ANGELES' 
|| CHR(39) ,
'OR UPPER(T'||rownum || '.' || 'CITY) = '  || CHR(39) || 'LOS ANGELES '|| CHR
(39)) col_val
FROM user_tab_columns where column_name = 'CITY'
ORDER BY ROWNUM

This query will give the following output.

WHERE UPPER(T1.CITY) = 'LOS ANGELES
OR UPPER(T2.CITY) = 'LOS ANGELES
OR UPPER(T3.CITY) = 'LOS ANGELES
OR UPPER(T4.CITY)= 'LOS ANGELES
OR UPPER(T5.CITY) = 'LOS ANGELES

You cannot use a UNION in this case at all as the output may not be in the order you want and "order by" clause is not allowed in UNION. Therefore, you will have to combine the two query results to get your final query. Please make sure you contact your DBA and confirm whether it is okay to run a query that results in a Cartesian product. Because I do not know how many tables in your database have the column CITY; nor do I know the size of these tables that you will be using to perform this query.

In that case you may want to run the queries separately. If you want to dynamically create the separate queries, run the following SQL statement

Set heading off;


SELECT 'SELECT * FROM ' || TABLE_NAME || ' WHERE UPPER(CITY) = ' || CHR(39)
 || 'LOS ANGELES' || CHR(39) || ';'
from user_tab_columns where column_name = 'CITY'

The result of this query will be

SELECT * FROM TABLE_1 WHERE CITY = 'LOS ANGELES'; 
..
..
SELECT * FROM TABLE_5 WHERE CITY = 'LOS ANGELES'; 

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close