Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    Amsterdam
    Posts
    18

    Unanswered: Outer join difficulty

    Can someone please help me with the following join ? I have two tables that I need to join. The select statement for the first part is as follows:-

    SELECT * FROM dbo.Holdings
    WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
    ORDER BY Sharename

    The table that is produced is as follows:-

    Rundate Accno Sharename NotAtHome
    20031128 260869 PANGBOURNE 123200
    20031128 260869 PARAMOUNT 221100
    20031128 260869 PRISAIB. 221100

    The table I wish to join on, is as follows:-

    SELECT * FROM dbo.Holdings
    WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
    ORDER BY Sharename

    The result is as follows:-

    Rundate Accno Sharename NotAtHome
    20031103 260869 PANGBOURNE 123200

    Note that Paramount and Prisaib are missing from the second table.

    I need to create an output table showing all three records with the missing two having 0 under "NotAtHome".

    I'd much appreciate any assistance with this..

    thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    (SELECT * FROM dbo.Holdings
    WHERE (Accno = '260869') AND (Rundate = '20031128') AND (Sharename LIKE 'P%')
    ORDER BY Sharename) q1
    LEFT OUTER JOIN
    (SELECT * FROM dbo.Holdings
    WHERE (Accno = '260869') AND (Rundate = '20031103') AND (Sharename LIKE 'P%')
    ORDER BY Sharename) q2
    ON q1.AccNo = q2.AccNo;

    Assuming AccNo is a key.

    The default value for non-matching rows is Null, however if you do indeed want a numerical then,

    I am not entirely sure of MSSQL syntax however I believe it's the following for Case statements.

    select attributes, (case NotAtHome If Null Then 0 Else NotAtHome END) As "NotAtHome"
    from
    (
    Above query
    )
    Last edited by r123456; 12-23-03 at 06:03.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would not need ORDER BY in the subqueries, and in fact the query is a bit neater without subqueries

    the NotAtHome column is weird, i left it as t1.NotAtHome as alkemac specified, but i think he/she might have meant t2.NotAtHome
    Code:
    select t1.Rundate 
         , t1.Accno 
         , t1.Sharename 
         , case when t2.AccNo is null
                then 0
                else t1.NotAtHome
            end as NotAtHome
      from dbo.Holdings t1
    left outer
      join dbo.Holdings t2
        on t1.AccNo = t2.AccNo
       and t2.Rundate = '20031103'
       and t2.Sharename like 'P%'
     where t1.Accno = '260869'
       and t1.Rundate = '20031128'
       and t1.Sharename like 'P%'
    rudy
    http://r937.com/
    Last edited by r937; 12-23-03 at 08:57.

Posting Permissions

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