CODEWITHSUNDEEP
sqloracle-database
Bob
Bob

Reputation: 563

Refer to other SQL SELECT statements aliased as tables in FROM clause

I have a very large query that follows the format below:

select ...
  from ( select field1,
                field2
           from some_table ) table1,
       ( select field1,
                field3
           from other_table ) table2

 where .....

Is is possible for me to refer to one of the tables "defined" in the from clause, lets say table1, in one of the other table definitions in the from clause?

For example:

select ....
  from ( select field1,
                field2
           from some_table ) table1,
       ( select table1.field1,
                field3
           from other_table,
                table1 ) table2

 where .....

Disclaimer: What I am trying to do is not as simple as the example above. The example is simply to illustrate the idea.

Upvotes: 0

Views: 511

Answers (2)

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

If you are using SQL 2005, you can use Common Table Expressions for doing what you are trying; Quassnoi gives us an example, in Oracle I don't know how to achieve it though

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425401

WITH
table1 AS
        (
        SELECT  field1, field2
        FROM    some_table
        ),
table2 AS
        (
        SELECT  field1, field2
        FROM    other_table, table1
        )
SELECT  *
FROM    table2

Upvotes: 4

Related Questions