CODEWITHSUNDEEP
sqloracle-databaseplsql
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33974

Accessing Outer Table inside 2nd Level Nesting Sub Query

Let's say I have,

select 
    (select SomColumn FROM MyInnerTable WHERE MyInnerTable.C1 = MyOuterTable.C2) AS FirstColumn
    ,(select SomColumn FROM (SELECT SomColumn FROM MyInnerTable2 WHERE MyInnerTable2.C1 = MyOuterTable.C2)) AS SecondColumn
form MyOuterTable

FirstColumn works but for SecondColumn its says MyOuterTable.C2 not found.

Upvotes: 0

Views: 40

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Oracle (and MySQL and perhaps some other databases) limit the scope of a table to one level in a subquery. Oops. Can't do it that way.

In this case, you should use LEFT JOIN anyway. Your query does not really need the subquery, but you can use:

select i.SomeColumn, i.SomeColumn as AS SecondColumn
form MyOuterTable o LEFT JOIN
     MyInnerTable i
     ON MyInnerTable.C1 = MyOuterTable.C2;

In more complex situations, you might find that lateral joins are what you need to solve the problem. If you need help with that, ask a new question with a better example query.

Upvotes: 2

Atif
Atif

Reputation: 2210

This works for me:

SELECT (select NAME FROM DEPARTMENT DEP WHERE DEP.DEPARTMENT_ID = EMP.DEPARTMENT_ID) AS DEPARTMENT_NAME,
(select NAME FROM (SELECT NAME FROM DEPARTMENT DEP WHERE DEP.DEPARTMENT_ID = EMP.DEPARTMENT_ID)) AS NEW_NAME
FROM EMPLOYEE EMP;

Are you doing anything different.

Upvotes: 0

Related Questions