Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

SQL Help?

  1. Nov 12, 2006 #1
    I'm having trouble getting the following work. Can anyone help me?

    I have two tables that look like the following:

    Code (Text):

    Table 1                 Table 2

    A B                     A B C
    1 4                     1 4 6
    2 5                          
    3 6
     
    How do I join both of them such that the end result would be this?

    Code (Text):


    Result

    A B C
    1 4 6
    2 5 null
    3 6 null

     
    I tried to do the right, left, normal joins, and union, but I still cannot get it to show like the resulting table. Any help would be great, thanks.
     
  2. jcsd
  3. Nov 12, 2006 #2

    -Job-

    User Avatar
    Science Advisor

    Try something like (tsql):
    Code (Text):

    SELECT A, B, NULL AS 'C' FROM Table1
    UNION ALL
    SELECT A, B, C FROM Table2
     
     
  4. Nov 12, 2006 #3
    Thanks for the reply. I just gave that a try and it did the following:

    Code (Text):


    Result

    A B C
    1 4
    2 5
    3 6
    1 4 6
     
    I'm using PSQL PostgresSQL to do this stuff. Should it be the same with your tsql code? Because I get the table above when doing your example.
     
  5. Nov 12, 2006 #4

    -Job-

    User Avatar
    Science Advisor

    Sorry, i misread what you wanted to do. How about the following:
    Code (Text):

    SELECT t1.A, t1.B, t2.C
        FROM Table1 AS t1
        JOIN Table2 AS t2 ON (t1.A = t2.A AND t1.B = t2.B)
     
     
  6. Nov 12, 2006 #5

    -Job-

    User Avatar
    Science Advisor

    Ok, the following worked for me:
    Code (Text):

    SELECT t1.A, t1.B, t2.C
        FROM Table1 AS t1
        JOIN Table2 AS t2 ON (t1.A = t2.A AND t1.B = t2.B)
    UNION ALL
    SELECT t1.A, t1.B, NULL AS 'C'
        FROM Table1 AS t1
        JOIN Table2 AS t2 ON (NOT t1.A = t2.A)
     
     
  7. Nov 12, 2006 #6
    Thanks, that worked, but what do you need to add if there are duplicate entires in Table 1?

    ie.

    Code (Text):


    Table 1

    A B
    1 4
    1 4
    2 5
    3 6

     
    Using the latest query you wrote, I get the resulting table:

    Code (Text):


    Table 1

    A B C
    1 4 6
    2 5
    3 6

     
    One of the same 1 4 entry is removed. I tried taking out the NOT, but still the same result.
     
  8. Nov 12, 2006 #7

    -Job-

    User Avatar
    Science Advisor

    That doesn't happen to me, it must be a PSQL implementation.
    Anyway, the following should work for you, even if it's not optimal:
    Code (Text):

    SELECT
    t1.A,
    t1.B,
    (SELECT t2.C FROM Table2 AS t2 WHERE t2.A = t1.A AND t2.B = t1.B) AS 'C'
    FROM Table1 AS t1
     
     
  9. Nov 12, 2006 #8
    That worked. Thanks a lot for your help!
     
  10. Nov 12, 2006 #9

    verty

    User Avatar
    Homework Helper

    My first try would be:
    select t1.A, t1.B, t2.C
    from Table1 t1
    left outer join Table2 t2 on t1.A = t2.A and t1.B = t2.B
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?



Similar Discussions: SQL Help?
  1. SQL columns (Replies: 5)

  2. CPU help (Replies: 12)

  3. WavePad help (Replies: 2)

Loading...