DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Subqueries in a Select

  1. #1
    Karthik Guest

    Subqueries in a Select


    Hello everyone

    I am facing a problem with using sub queries in a select statement. I am
    running this on Oracle 8.0.5...

    I have this table table_a and I some 4-5 fields that are foreign keys with
    a table table1 and another 4-5 fields that are foreign keys with a table
    table2..

    am doing this select on table_a and am selecting a bunch of fields..

    select a.1, a.2, a.3.......etc...now I have to translate these fk fields
    to their description..

    select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4), (select
    tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
    tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
    tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
    table_z where blah blah blah...

    I have a similar query in SQL Server that works. I didnt think writing sql
    statements in Oracle was quite different from SQL Server. any suggestions
    on this ???

    i dont want to use 4 aliases for table_1 and table_2...

    Thanks for all the help in advance..

    Regards
    Karthik

  2. #2
    Simon Sellick Guest

    Re: Subqueries in a Select


    "Karthik" <kemmaneni@apshealthcare.com> wrote:
    >
    >Hello everyone
    >
    >I am facing a problem with using sub queries in a select statement. I am
    >running this on Oracle 8.0.5...
    >
    >I have this table table_a and I some 4-5 fields that are foreign keys with
    >a table table1 and another 4-5 fields that are foreign keys with a table
    >table2..
    >
    >am doing this select on table_a and am selecting a bunch of fields..
    >
    >select a.1, a.2, a.3.......etc...now I have to translate these fk fields
    >to their description..
    >
    >select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4),

    (select
    >tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
    >tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
    >tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
    >table_z where blah blah blah...
    >
    >I have a similar query in SQL Server that works. I didnt think writing sql
    >statements in Oracle was quite different from SQL Server. any suggestions
    >on this ???
    >
    >i dont want to use 4 aliases for table_1 and table_2...
    >
    >Thanks for all the help in advance..
    >
    >Regards
    >Karthik


    Karthik,
    I think aliases are the ANSI way to join to multiple rows of the same table:

    SELECT
    t.field,
    a1.name,
    a2.name ...
    FROM
    t,
    a AS a1,
    a AS a2
    WHERE
    a1.code = t.code1
    AND
    a2.code = t.code2

    But your suggestion that Oracle and SQL Server share the same query syntax
    is incorrect - neither of them follows the ANSI standard beyond the form
    of trivial queries.


  3. #3
    Qbert Guest

    Re: Subqueries in a Select


    "Karthik" <kemmaneni@apshealthcare.com> wrote:
    >
    >Hello everyone
    >
    >I am facing a problem with using sub queries in a select statement. I am
    >running this on Oracle 8.0.5...
    >
    >I have this table table_a and I some 4-5 fields that are foreign keys with
    >a table table1 and another 4-5 fields that are foreign keys with a table
    >table2..
    >
    >am doing this select on table_a and am selecting a bunch of fields..
    >
    >select a.1, a.2, a.3.......etc...now I have to translate these fk fields
    >to their description..
    >
    >select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4),

    (select
    >tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
    >tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
    >tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
    >table_z where blah blah blah...
    >
    >I have a similar query in SQL Server that works. I didnt think writing sql
    >statements in Oracle was quite different from SQL Server. any suggestions
    >on this ???
    >
    >i dont want to use 4 aliases for table_1 and table_2...
    >
    >Thanks for all the help in advance..
    >
    >Regards
    >Karthik


    Umm... Have you considered using a normal join?
    you know.
    Select a1, a2, a3, tbl1.name
    From t1, t2, t3
    where a.a4 = tbl1.id

    Keep in mind you may have to peform some outer joins on these tables.
    which you should be able to accomplish by using (+) operand. after where
    like where a.a4 = tbl1.id(+)

    In addition the method of doing in statements is increadibly slow when compared
    to using joins.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center