Hi, I am trying to join the results from one select statement with 2
other tables and am not sure of the syntax. What comes natural doesnt
seem to work:

ie

SELECT * FROM tableC,
(select columnA, columnB.....
from table A, table B.....
where "TableA.fieldA = tableB.fieldA AND ......),
WHERE (tableC.fieldA = tableA.fieldB) AND (tableC.fieldB =
tableB.fieldB);

how can this be written? I could create a temporary table to replace
the select query in brackets but this seems a bit longwinded.

On Jun 7, 9:35 am, colmkav <colmj...@yahoo.co.uk> wrote:
> Hi, I am trying to join the results from one select statement with 2
> other tables and am not sure of the syntax. What comes natural doesnt
> seem to work:
>
> ie
>
> SELECT * FROM tableC,
> (select columnA, columnB.....
> from table A, table B.....
> where "TableA.fieldA = tableB.fieldA AND ......),
> WHERE (tableC.fieldA = tableA.fieldB) AND (tableC.fieldB =
> tableB.fieldB);
>
> how can this be written? I could create a temporary table to replace
> the select query in brackets but this seems a bit longwinded.

This is not a subquery, but an inline view.
Inline views need to be aliased (so outside the closing ')' ) and the
where clause needs to refer to the view alias so
SELECT * FROM tableC,
(select columnA, columnB.....
from table A, table B.....
where "TableA.fieldA = tableB.fieldA AND ......) view_alias --
Note I removed the redundant ,
WHERE (tableC.fieldA = view_alias.columnB) AND (tableC.fieldB =
view_alias.columnB);

--
Sybrand Bakker
Senior Oracle DBA