#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Question Joining two tables?


    Dear Gurus,

    I am facing some difficulties in joining two tables to display all my records. Here are the sample tables:

    Table #1:
    Name | year | late attendance
    Andy | 2010 | 20
    Andy | 2011 | 11
    Jack | 2009 | 5

    Table #2:
    Name | year | early attendance
    Andy | 2009 | 5
    Andy | 2010 | 10
    Joe | 2011 | 3

    What I would like displayed is:
    Name | year | late attendance | early attendance
    Andy | 2009 | | 5
    Andy | 2010 | 20 | 10
    Andy | 2011 | 11 |
    Jack | 2009 | 5 |
    Joe | 2011 | | 3

    I tried using the joins but I am not able to get it working properly..

    Do hope someone can help point me to the right direction to get the display above. Thank you.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,612
    Rep Power
    1945
    Can you post the queries you have tried?

    Any reason you have two tables?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Use a full outer join.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    103
    Rep Power
    9
    this is what you need in my case it is 2 tables #earlyAttendance and #lateAttendance

    Code:
    select ISNULL(e.name,l.Name) as name, 
    ISNULL(e.[year],l.[year]) as [year], 
    l.[late attendance],
    e.[early attendance]
    from #earlyAttendance e
    full join #lateAttendance l
    on e.Name=l.Name

IMN logo majestic logo threadwatch logo seochat tools logo