How to use tables returned from subquery in SQL query in Oracle

Question

I have a required table like

+--------+----------+-----------------+--------------------+-------------------+
|offer_id|Offer_name|Total_offers_sold|total_device_changed|total_offer_changed|
+--------+----------+-----------------+--------------------+-------------------+

now, For first three columns I have found the data using joins and then grouping by offer ID

+--------+----------+-----------------+
|offer_id|Offer_name|Total_offers_sold|
+--------+----------+-----------------+
|12      |abc       |23               |
+--------+----------+-----------------+
|23      |gdf       |3                |
+--------+----------+-----------------+
|54      |df        |54               |
+--------+----------+-----------------+
|56      |gf        |4                |
+--------+----------+-----------------+
|65      |ad        |17               |
+--------+----------+-----------------+
|75      |hg        |54               |
+--------+----------+-----------------+

For other two columns i.e. offer_changed and total_device_changed, they are themselves complex queries to find the required data. lets, say I have this sample data found by executing query for those two clumns.

+--------+-------------------+
|offer_id|total_offer_changed|
+--------+-------------------+
|12      |3                  |
+--------+-------------------+
|56      |65                 |
+--------+-------------------+
|65      |4                  |
+--------+-------------------+

similarly,

+--------+--------------------+
|offer_id|total_device_changed|
+--------+--------------------+
|12      |2                   |
+--------+--------------------+
|23      |5                   |
+--------+--------------------+
|75      |20                  |
+--------+--------------------+

Now the problem is, these are temporary results and I am unable to understand how to merge the results of this output(tables) in the bigger query corresponding to their offer ID's.i.e the final result I need is :

+--------+----------+-----------------+--------------------+-------------------+
|offer_id|Offer_name|Total_offers_sold|total_device_changed|total_offer_changed|
+--------+----------+-----------------+--------------------+-------------------+
|12      |abc       |23               |2                   |3                  |
+--------+----------+-----------------+--------------------+-------------------+
|23      |gdf       |3                |5                   |                   |
+--------+----------+-----------------+--------------------+-------------------+
|54      |df        |54               |                    |                   |
+--------+----------+-----------------+--------------------+-------------------+
|56      |gf        |4                |                    |65                 |
+--------+----------+-----------------+--------------------+-------------------+
|65      |ad        |17               |                    |04                 |
+--------+----------+-----------------+--------------------+-------------------+
|75      |hg        |54               |20                  |                   |
+--------+----------+-----------------+--------------------+-------------------+

please help

do you want the final table as output in your query? – Hamidreza Jan 13 '14 at 04:59 — Hamidreza, Jan 13 '14 at 04:59
Yes! I wanted the final table as query – Incredible Jan 15 '14 at 13:03 — Incredible, Jan 15 '14 at 13:03

score 0 · Answer 1 · edited May 23 '17 at 12:21

The thing that you are looking for is an inline view. An inline view is a subquery in from clause. Unlike subqueries in select and where clauses, you cannot refer to the other tables in your from clause, but you can join the result of the inline query with the tables in your main query.

In your case it would be:

select offer_id, offer_name, total_offers_sold
  from table_name t1
       inner join (select offer_id, total_offer_changed
                     from table_name
                    where whatever...
                   ) t2
            on t1.offer_id = t2.offer_id
 where whatever_the_other_conditions
;

But, in real world I would simply try to do something like

select offer_id, offer_name, total_offers_sold, total_offer_changed
  from table_name
 where whatever_the_conditions
;

Read more about inline views on AskTom.com and this SO question.

score 0 · Accepted Answer · answered Jan 13 '14 at 05:17

try this query:

SELECT
t1.OFFER_ID,t1.OFFER_NAME,t1.TOTAL_OFFERS_SOLD,
t3.TOTAL_DEVICE_CHANGED,t2.TOTAL_OFFER_CHANGED 
FROM
TABLE1 t1
LEFT OUTER JOIN TABLE2 t2 ON t1.OFFER_ID = t2.OFFER_ID
LEFT OUTER JOIN TABLE3 t3 ON t1.OFFER_ID = t3.OFFER_ID 
ORDER BY t1.OFFER_ID;

How to use tables returned from subquery in SQL query in Oracle

2 Answers2