selecting data from 3 tables

shajju · #1 (**permalink**) 02-13-12, 00:56

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

Littlefoot · #2 (**permalink**) 02-13-12, 01:08

Quote:

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?

shajju · #3 (**permalink**) 02-13-12, 03:50

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.

tonkuma · #4 (**permalink**) 02-13-12, 05:09

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

shajju · #5 (**permalink**) 02-13-12, 06:44

Ofcourse. I could I miss that! Must admit, dbforums never lets me down

Thanks guys.

Internet Services

Web Development

Digital Marketing

Consumer Tech