-4

for a user (user A) I have several tables (table1, table2, ..., table n) that have the same fields (field1, field2, field3, field4), I would like to know the name of the table containing the value x for field 4 (where field4 = x)

Hammou Zine Meddour
  • 1
  • 1
  • 1
    "I have several tables (table1, table2, ..., table n) that have the same fields (field1, field2, field3, field4)". I smell a design flaw. There should be no need for this in a normalised database. – ADyson Dec 19 '17 at 16:02
  • 2
    Welcome to Stack Overflow. Perhaps you should read this first: https://stackoverflow.com/help/how-to-ask – Verv Dec 19 '17 at 16:02
  • @a_horse_with_no_name - not sure this is an exact duplicate. It seems in this case the OP knows the table names in advance, and knows exactly in which column to look in each table. This has a much simpler solution than the thread you linked to. –  Dec 19 '17 at 16:13

1 Answers1

0

By way of example: User SCOTT (the standard Oracle user/schema) has two tables, EMP and DEPT, both with a column DEPTNO. I want to find which table (or tables) has the value :x in column DEPTNO. (Note how I allow the value I am searching for to be a bind variable - so I can reuse the query for different values.) I am only interested in identifying the table names, so I stop a search in each table as soon as the value is found.

select  'EMP' as table_name
  from  scott.emp
  where deptno = :x and rownum = 1
union all
select  'DEPT' as table_name
  from  scott.dept
  where deptno = :x and rownum = 1
---  etc.  (union all for all tables of interest)

Note that the fact that the tables all have the same columns is irrelevant. Each table must have ONE column that might contain the value :x (the assumption is that this one column will have the same data type in all tables).

Note also that the user running this query must have privileges to the tables in the SCOTT schema (obviously!)

  • Thank you for answering,but the columns are identical in all the tables (and I do not know the number of tables (n) and the value "x" is contained in a single table – Hammou Zine Meddour Dec 19 '17 at 17:02