Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: selecting data from 3 tables

    Hi

    Hope my query will be simple to understand.

    I have 3 tables in the same schema which have the same primary keys. Each has 3 primary keys.
    I need to select one column each from each table but if I use aliases, I get too many rows.
    Code:
    select a.datetime, a.bbb, a.ccc, a.col1+b.col1+c.col1 abc
    from table1 a, table2 b, table3 c
    where a.bbb=b.bbb
    and b.bbb=c.bbb
    and a.ccc=b.ccc
    and b.ccc=c.ccc
    Any input is much appreciated.

    Regards

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Each has 3 primary keys
    That's impossible.

    Perhaps you should post a test case (CREATE TABLE & INSERT INTO several sample records) so that we could see what's you really have.

    Just wondering: what is "I get too many rows" for you? Did you mean to say that query returns (for example) 1000 records while you *know* it should return only 10, or did Oracle raise TOO-MANY-ERRORS error?

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    What I meant to say was each table has the same primary keys:
    'Datetime', 'bbb' and 'ccc'.

    The number of rows returned should only be 1500 but millions of records are being returned.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You should add Datetime to WHERE clause.

    For example:

    sample data:
    Code:
    table 1            | table 2
    -------------------+-------------------
    bbb ccc datetime   | bbb ccc datetime
    -------------------+-------------------
    1   1   2012-02-11 | 1   1   2012-02-11
    1   1   2012-02-12 | 1   1   2012-02-12
    -   -   -          | 1   1   2012-02-13
    Result of
    FROM table1 a , table2 b
    WHERE a.bbb = b.bbb AND a.ccc = b.ccc
    Code:
    table 1            | table 2
    -------------------+-------------------
    bbb ccc datetime   | bbb ccc datetime
    -------------------+-------------------
    1   1   2012-02-11 | 1   1   2012-02-11
    1   1   2012-02-11 | 1   1   2012-02-12
    1   1   2012-02-11 | 1   1   2012-02-13
    1   1   2012-02-12 | 1   1   2012-02-11
    1   1   2012-02-12 | 1   1   2012-02-12
    1   1   2012-02-12 | 1   1   2012-02-13
    Result of
    FROM table1 a , table2 b
    WHERE a.bbb = b.bbb AND a.ccc = b.ccc AND a.datetime = b.datetime
    Code:
    table 1            | table 2
    -------------------+-------------------
    bbb ccc datetime   | bbb ccc datetime
    -------------------+-------------------
    1   1   2012-02-11 | 1   1   2012-02-11
    1   1   2012-02-12 | 1   1   2012-02-12
    Last edited by tonkuma; 02-13-12 at 06:26. Reason: Add sample.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Ofcourse. I could I miss that! Must admit, dbforums never lets me down

    Thanks guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •