Results 1 to 5 of 5

Thread: outer join

  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: outer join

    I need help with a outer join.

    I have a table called raw_data_info ..
    raw_data_id raw_data_name
    1 net income
    2 net assets


    I have another table called raw_data
    raw_data_id report_date amount
    1 2/28/2000 50


    I need to write a sql statement that will return the below

    raw_data_name amount
    net income 50
    net assets

    I want a blank for net assets.

    Is this possible?
    Last edited by abe6162; 12-09-04 at 14:27.

  2. #2
    Join Date
    Aug 2004
    Posts
    99
    It is possible:

    select a.raw_data_name, b.amount
    from
    raw_data_info a, outer raw_data b
    where
    a.raw_data_id = b._raw_data_id

    you just have to use the outer on the table that you want to get nulls from

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    I am sorry. I left one column out. I didn't think it mattered, but I see it does now. I am doing this in Access.

    The tables should be -- raw_data_info
    raw_data_id -- raw_data_name
    1 -- net income
    2 -- net assets


    I have another table called raw_data
    raw_data_id -- report_date -- cmpy_num --- amount
    1 -- 2/28/2000 -- 9999 -- 50
    1 -- 2/28/2000 -- 8888 -- 100

    I tired (but it didn't work) --

    SELECT I.mdy_raw_data_name, D.amount
    FROM raw_data_info AS I LEFT JOIN raw_data AS D ON I.raw_data_id=D.raw_data_id
    WHERE (report_date=#2/28/1999# Or report_date Is Null)

    Is there any way to return --
    raw_data_name -- amount -- cmpy_num
    net income -- 50 -- 9999
    net assets -- -- 9999
    net income -- 100 -- 8888
    net assets -- -- 8888

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select I.mdy_raw_data_name
         , D.amount
      from raw_data_info AS I 
    left outer
      join raw_data AS D 
        on (
           I.raw_data_id = D.raw_data_id
       and iif(isnull(D.report_date)
              ,#2/28/1999#
              ,D.report_date) = #2/28/1999#
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    24
    Thank you so much!!

Posting Permissions

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