Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: All from one table and all from another

    I'm hoping someone could help me write the SQL code to solve this problem

    I have two tables and a master one if I need it. All tables can be linked with the Master_ID. Table1 and Table2 can each have 0, one or many records for each Master_ID. The other column in the two tables is a number representing a volumn of two different fluids.

    Table1
    Master_id
    Volume1_amount

    Table2
    Master_id
    Volume2_amount

    Master
    Master_id
    Master_name

    How can I return all of the rows in Table1 and all of the rows in Table2 for each Master_id such that it looks like this if Table1 has 2 records and Table2 has 1 record for a given Master_id and then Table2 has 2 records and Table1 has 0 for a differnt Master_id

    Master_id Volume1_amount Volume2_amount
    100235 25.3 m 62.1 m
    100235 22.0 m null
    220000 null 85.66 m
    220000 null 59.0 m

    Any help would very much be appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: All from one table and all from another

    What are the primary keys of Table1 and Table2? What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    The tables are actually temporary tables so there is/are no primary key(s) define but the Master_id is what links them all together. The master_id in Table1 will match the Master_id in Table2 which both match to Master_id in the master table

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, but my other question was:

    What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?

    You haven't answered that.

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    Oh sorry - nothing except for which ever is first in the table. The two volumes don't relate to each other at all except that they both relate to the master_id. Make sense?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, well the concept of "first in the table" is meaningless in a relational database without something to order by. What DBMS are you using? For Oracle I know a trick you can use. Otherwise, I would suggest you need to add an extra column to Table1 and Table2:

    Table1
    Master_id
    Volume1_amount
    Seq_no

    Table2
    Master_id
    Volume2_amount
    Seq_no

    where Seq_no is 1 for the 1st record for each Master_id, 2 for the second etc.

    Then your query becomes:

    select coalesce(t1.master_id,t2.master_id), t1.volume1_amount, t2.volume2_amount
    from t1
    full outer join t2
    on
    (t1.master_id = t2.master_id
    and t1.seq_no = t2.seq_no
    );

  7. #7
    Join Date
    Jan 2004
    Posts
    4
    Excellent! Thank you very much

Posting Permissions

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