Name table resulting from SELECT

Question

I need to make a SELECT of the table resulting from another SELECT, but I need to refer it to ON of INNER JOIN. How do I do this in Oracle (PL/SQL)? I tried it this way but it didn’t work:

SELECT * FROM (SELECT * FROM recurso WHERE tipo = 'CID') AS cidade INNER JOIN hierarquia ON hierarquia.recurso = cidade.codigo; 

Answer 1

You must position the Subselect in front of the INNER JOIN and then you can give a nickname to your selection as if it were a table.

SELECT * 
FROM hierarquia 
INNER JOIN (
   SELECT *
   FROM recurso 
   WHERE tipo = 'CID'
) Cidade
ON Cidade.codigo = hierarquia.recurso 

Sqlfiddle - Online example