Query tables and columns based on table data

Question

Without knowing the name of a table and columns, I want to query the database retrieve the table and column names and then query the given tables.

I have an Oracle database schema that is like the following:

Item table:

Item_id, Item_type, 
=================
1   box
2   book
3   box

Book table:

Item_id, title,     author
===========================
2   'C# Programer', 'Joe'

Box table:

Item_id, Size
=====================
1,  'Large' 
3,  'X Large'

Column_mapping table

Item_type, column_name, display_order
=====================================
box,       Size,    1
book,      title,   1
book,      author   2

Table_mapping table:

Item_type,  Table_name
========================
box,        Box
book,       Book

I would like a SQL statement that would give something like the following results:

Item_id, Item_type  column1  column2
====================================
1,  box,        'Large',       <null>
2,  book,       'C# Programer', 'Joe'
3,  box,        'X Large',     <null>

When I tried the simplified query

select * 
from 
   (select Table_name
    from Table_mapping
    where Item_type = 'box')
where 
   Item_id = 1;

I get an error that Item_id is invalid identifier

and if I try

select * 
from 
    (select Table_name
     from Table_mapping
     where Item_type = 'box');

I just get

Table_name
===========
Box

I am not sure how to proceed.

Conrad Frix · Accepted Answer · 2012-03-26 16:35:39Z

3

One way is to join both table and then use a coalesce on the column that can contain data from either table

SELECT 
    i.Item_id,
    i.Item_type,
    COALESCE(b.title, bx.size)  column1,
     b.author   column2
FROM
   Item i
   LEFT JOIN Book b
   ON i.item_id = b.item_id
   LEFT JOIN Box bx
   ON i.item_id = bx.item_id

Depending on how large your datasets are you may want to add a filter on the join e.g.

   LEFT JOIN Book b
   ON i.item_id = b.item_id
       and i.item_type = 'book'
   LEFT JOIN Box bx
   ON i.item_id = bx.item_id
       and i.item_type = 'box'

See it work at this SQLFiddle

If you wanted to do something based on the data in table_mapping or column_mapping you'd need to use dynamic SQL

edited Mar 26, 2012 at 16:35

answered Mar 26, 2012 at 16:21

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dlb Over a year ago

This is not quite what I need. The table_mapping will have about 15 to 20 tables in it, and will be different for different clients. So I do need something base on the data in the table_mapping and column_mapping. So I am looking at a PL/SQL solution. Thanks fo the SQLFiddle link.

barsju · Accepted Answer · 2012-03-26 16:19:53Z

0

Basically it is two separate queries. One for boxes and one for books. You can use union to merge the result sets together.

select i.Item_id, i.Item_type, b.size, null
from Item i inner join Box b on i.Item_id=b.Item_id
where i.Item_type = "box"
UNION
select i.Item_id, i.Item_type, b.title, b.author
from Item i inner join Book b on i.Item_id=b.Item_id
where i.Item_type = "book"

answered Mar 26, 2012 at 16:19

barsju

4,4561 gold badge23 silver badges24 bronze badges

Comments

David Faber · Accepted Answer · 2012-03-26 16:21:06Z

0

ORACLE actually stores the table- and column names in its data dictionary, so there is no need for you to maintain those data separately. Try this to get the table names:

SELECT table_name FROM user_tables;

Then do this to get the columns for each table:

SELECT column_name FROM user_tab_columns WHERE table_name = 'MYTABLE';

Once you do that, you will need to create a stored procedure in order to execute dynamic SQL. I don't think you can do this in a plain-vanilla query.

answered Mar 26, 2012 at 16:21

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Collectives™ on Stack Overflow

Query tables and columns based on table data

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related