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

    Unanswered: selecting from 2 tables in different schemas

    Hi

    Running Oracle 9i.

    I have a query that selects from 2 tables each in a different schema. Table 1 contains 50 columns, Table 2 contains 60 columns however the first 3 columns in both tables are identical....Datetime, BSC and CELL.

    I wrote a formula which uses columns from the first table and just one column from the second table. When I run the SQL, it just takes ages and ages to run like atleast 10 min to run, after which I just cancelled the query.

    If I say
    Select A.datetime, A.BSC, A.cell,A.column4, A.column5
    from table 1 a, table 2 b
    where A.CELL=B.CELL or even A.CELL <> B.CELL

    The result is the same cell repeated many times (maybe for all the cells in both tables). In short the result didn't look good to me.

    Am I doing something wrong here? Does the number of columns in both tables have to be the same if I want to select columns from both tables in a single query?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Query you wrote is invalid in syntax. If you expect any kind of help, please, post accurate information.

    Your query doesn't select any column from "table 2". Do you need "table 2" at all?

    Which table belongs to which schema? How do you access a table from a different schema?

    What "formula" are you talking about? I don't see any formula here.

    What is the expected result?

    Perhaps you should create a small and meaningful test case, show what you have and what you'd like to get. Because, all I can say (regarding your "am I doing something wrong here" question) is: yes, you must be doing something wrong.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Apologies and thanks.

    Select A.datetime, A.BSC, A.cell,A.column4, A.column5+B.column1 XXX
    from schema1.table_1 A, schema2.table_2 B
    where A.CELL=B.CELL or even A.CELL <> B.CELL

    Expected result is all the cells in schema1.table_1 with each having a corresponding value of XXX.

    Sorry, not sure how to create the test-case.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    where A.CELL=B.CELL or even A.CELL <> B.CELL
    What's that?

    Test case includes CREATE TABLE and INSERT INTO statements so that we could reproduce your environment. Having input data, show what is expected output.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Littlefoot View Post
    How do you access a table from a different schema?
    WHICH, I think is the original question

    SCHEMA A has a Table Table1, and SCHEMA B has a Table Table1

    How do I join those 2 tables if the PK in each table is the same, and the column is called ID.

    Can you show us an example of that join?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    SELECT a1.foo, b1.bar ...
    FROM schema_a.table1 a1
    JOIN schema_b.table1 b1 ON a1.id = b1.id
    WHERE a1.foo > 300

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Littlefoot View Post
    Query you wrote is invalid in syntax. If you expect any kind of help, please, post accurate information.

    Your query doesn't select any column from "table 2". Do you need "table 2" at all?
    It must be an Eastern European thing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What must be an EE thing?

    If it is about us (Croatians), well, we love to think about ourselves as Central Europeans, although during the history we were in Southeastern Europe and Balkans as well. The truth is that - most of the time - we're in deep shit (pardon my French).

Posting Permissions

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