Results 1 to 4 of 4

Thread: Left Join

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Left Join

    Hi: Can any one please tell me how do i change my query so that it also shows the records from table Main which are not in table [star Redemption]. I try to use the LEFT Join but it gives syntax error.

    Thanks.


    SELECT q1.Name, q1.Tot-q2.Tot AS StarBalance
    FROM [Select [Name], Sum(Main.[#_Stars]) as Tot
    From Main
    Group By [Name]
    ]. AS q1 INNER JOIN [Select [CRC], Sum(Val(Replace(nz(Prize,0),"$",""))) as Tot
    From [star Redemption]
    Group By [CRC]
    ]. AS q2 ON q1.Name = q2.CRC;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select q1.Name
         , q1.Tot - q2.Tot as StarBalance
      from (
           select Name
                , sum(Main.[#_Stars]) as Tot
             from Main
           group 
               by Name
           ) as q1 
    left outer        
      join (
           select CRC
                , sum(Val(Replace(nz(Prize,0),"$",""))) as Tot
             from [star Redemption]
           group 
               by CRC
           ) as q2 
        on q1.Name = q2.CRC
    if your error message does not go away, you may want to tell us what it said
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    157
    It shows the name but it not shows the StarBalance of those which are not in [star Redemption] but in table Main
    Name StarBalance
    Acito Lisa 22
    Alfarone, Valerie
    Attanasio, Jennifer
    Barnes, Sylvia 5
    Bellissimo, Helen 10
    Bernardez, Maria 20
    Bertrand, Karen 20
    Bos, Beverly 12
    Breuer, Dorothy 8
    Brown, Gloria 11
    Carle, Miki 7
    Castellano, Matthew 15
    Caulfied, Bridget

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change it to:
    Code:
    q1.Tot - nz(q2.Tot,0) as StarBalance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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