1. Not finding help here? Sign up for a free 30min tutor trial with Chegg Tutors
    Dismiss Notice
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...