Re: subqueries

On Jun 7, 9:35 am, colmkav <colmj..._at_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