CODEWITHSUNDEEP
sqloracle-databaseunion
KarmaOfJesus
KarmaOfJesus

Reputation: 101

UNION table in second query based on the result of first query in Oracle sql

I am struggling with following problem (in further I provide pseudocode in order to make my example more understandable)

Assume I have 2 queries which result I want to union with each other

What query1 output looks like:

ID   OFFER
1    prod_1
2    prod_2
3    prod_2
4    prod_1

What query2 output looks like:

ID   SEGMENT
1    LOW
2    HIGH
3    MED
999  MED

What I need to do is to union results of this 2 queries, but avoid taking row with ID = 999

If there any way to do it using UNION by extracting from query2 rows bases on values of column ID which are present in column ID of query2?

I know that following code is incorrect but it conveys the idea of ​​the question greatly:

--query1
(
 SELECT ID, OFFER
 FROM TAB1
 WHERE ID <= 4
) RES1
UNION
--query2
SELECT ID, SEGMENT
FROM TAB1
WHERE ID IN (SELECT ID FROM RES2)

Result should be as following

ID   OFFER
1    prod_1
2    prod_2
3    prod_2
4    prod_1
1    LOW
2    HIGH
3    MED

Appreciate your help

Upvotes: 3

Views: 2993

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Your pseudo code comes very close. You can use WITH for convenience:

WITH q1 AS (SELECT id, offer FROM tab1 WHERE id <= 4)
   , q2 AS (SELECT id, segment FROM tab1 WHERE id IN (SELECT id FROM q1))
SELECT * FROM q1
UNION ALL
SELECT * FROM q2;

(Be aware though that you can get the rows in any order, if you don't specify an ORDER BY clause.)

Upvotes: 6

Related Questions