Problem with full join

• Tom McGehee

  Old Hand

  Points: 344

  More actions

  January 26, 2005 at 8:56 am

  #60388

  I am trying to do a full outer join as follows:

    select *

      from account

      full outer join balances

        on balances.company = account.company and

           balances.account_no = account.account_no and

     balances.fiscal_year = '2004'

      where account.company = '14'

  I am getting the same results as I do with a left outer join.  There are records in the balances table that do not have matches in the account table that are not showing up.  What am I doing wrong?

   

  Thanks

  Tom

     

   

   

   

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  January 26, 2005 at 9:21 am

  #539169

  When you use a WHERE clause on an outer-joined table you essentially convert the join type back to Inner.

  select *

  from account

  full outer join balances

    on balances.company = account.company and

       balances.account_no = account.account_no and

       balances.fiscal_year = '2004' and

       account.company = '14'