CODEWITHSUNDEEP
sqloracle-databasefull-outer-join
Marcus Leon
Marcus Leon

Reputation: 56659

Oracle outer join "entity"

How can you refer to the "combined entity" created from an outer join within your query? Specifically how can you replace the "??" in the query below:

SELECT TableA.x, 
       ??.y --How do you select from the combined entity?
  FROM TableA, 
       TableB buys 
FULL OUTER JOIN TableB sells ON buys.run_id = sells.run_id 
                            AND buys.specie_id = sells.specie_id 
                            AND buys.account_id = sells.account_id
WHERE TableA.id = ?? 
  AND -- want to join this with the "combined entity" resulting from the outer join
      buys.buy = 'Y' 
  AND -- Is this valid or does this have to be within the Outer join statement?
      sells.buy = 'N';

Upvotes: 2

Views: 226

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132570

You need the sells.buy = 'N' and buys.buy = 'Y' predicates inside the outer join.

SELECT TableA.x, 
       ??.y --How do you select from the combined entity?
  FROM TableA, 
INNER JOIN TableB buys ON <whatever>
                      AND buys.buy = 'Y' 
FULL OUTER JOIN TableB sells ON buys.run_id = sells.run_id 
                            AND buys.specie_id = sells.specie_id 
                            AND buys.account_id = sells.account_id
                            AND sells.buy = 'N'

As for ??.y, you need to specify whichever table you want it from - TableA, buy or sell.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

select TableA.x, fullTable.y 
from TableA 
  INNER JOIN
    ( SELECT y
        FROM TableB buys
          full outer join TableB sells
            on buys.run_id = sells.run_id
            and buys.specie_id = sells.specie_id
            and buys.account_id = sells.account_id
            AND buys.buy = 'Y' AND sells.buy = 'N'    
    ) AS fullTable
    ON TableA.id = fullTable.y

The condition can be in the final WHERE or ON like this but it essentially cancels the full join:

select TableA.x, fullTable.y 
from TableA 
  INNER JOIN
    ( SELECT y
           , buys.buy AS buysBuy             --- fields here so they can be
           , sells.buy AS sellsBuy           --- used in the final WHERE or ON
        FROM TableB buys
          full outer join TableB sells
            on buys.run_id = sells.run_id
            and buys.specie_id = sells.specie_id
            and buys.account_id = sells.account_id                
    ) AS fullTable
    ON TableA.id = fullTable.y
    AND buysBuy = 'Y' AND sellsBuy = 'N'    --- the condition here

Upvotes: 3

Allan
Allan

Reputation: 17429

Presumably what you're trying to ask is how to refer to the columns used in the join in other parts of the query. There is no joined entity per se; you still have to refer to the columns from the tables (in this case "buys" or "sells", it just happens that the columns used in the join will have the same value for both tables.

If you want whichever is not null (since this is a full outer join), you can use coalesce or nvl to find find the non-null value:

SELECT TableA.x, 
       nvl(buys.run_id,sells.run_id) as run_id,
       ...

Upvotes: 2

Related Questions