CODEWITHSUNDEEP
sqloracle-database
Basti Funck
Basti Funck

Reputation: 1439

Combine a SELECT and a UNION ALL

Following two queries:

1.

SELECT idA, idB FROM tableA WHERE idA = 1;

2.

SELECT idB, value FROM tableB1 WHERE idB = <the idB result of query 1>
UNION ALL
SELECT idB, value FROM tableB2 WHERE idB = <the idB result of query 1>;

At the moment I'm storing the result from the first query in a variable and then execute the 2nd query seperatly, but I'm trying to combine these two queries so I only have to execute one query at all.

Query 1 returns exact one row, so we won't have multiple idB values. And there is exactly one entry in either tableB1 or tableB2 that matches this particular idB value.

I can't seem to find a way to combine these two queries into one, without having the data space explode in one place. For example the naive approach:

SELECT 
    idA, idB 
FROM 
    tableA a,
LEFT JOIN
    (SELECT idB, value FROM tableB1
     UNION ALL
     SELECT idB, value FROM tableB2) b ON b.idB = a.idB
WHERE idA = 1;

Which means I merge two complete tables, which is obviously an overkill.

Upvotes: 0

Views: 79

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

I'm unclear on what you want. Looks like you want to go from idA to Value through TableA and either TableB1 or TableB2, which ever one has a matching Value.

SELECT A.idA, A.idB, COALESCE(B1.Value, B2.Value)
FROM tableA A
WHERE idA = 1;
LEFT JOIN TableB1 B1 ON B1.idB = A.idB
LEFT JOIN TableB2 B2 on B2.idB = A.idB

Upvotes: 4

Related Questions