SQL SRV LEFT OUTER JOIN help

• scranthdaddy

  Mr or Mrs. 500

  Points: 552

  More actions

  June 4, 2004 at 8:59 am

  #83007

  select

  BORR.fico as borr1fico,

  BORR2.fico as borr2fico,

  BORR3.fico as borr3fico

  from tblloan EX with (nolock)

  inner join borrower BORR with (nolock) on BORR.Id = EX.ApplId

  left outer join borrower BORR2 with (nolock) on BORR2.Borr_Id = EX.LoanApplId and BORR2.Borr_Id = '2'

  left outer join borrower BORR3 with (nolock) on BORR3.Borr_Id = EX.LoanApplId and BORR3.Borr_Id = '3'

  where EX.ApplId = '1234567890'

  and BORR.Borr_Id = '1'

  Hi,

  I would like to left outer join on a table that is on the right side of a left outer join.

  For ex, I have a join between the export and borrower table (1:M). I would like to add another outer join

  to a borrower_address table from the borrower table, but I am not sure how to accomplish this. An example

  of the end result I a looking for would be:

  borr1fico borr2fico borr3fico borr1street borr1city borr1state borr2street borr2city borr2state

  --------

  700 650 800 113 Elm Mpls MN 504 Oak StPaul null

  
  Andrew J. Hahn

• Don Healey

  Old Hand

  Points: 333

  More actions

  June 7, 2004 at 4:09 am

  #509191

  Try using a temporary table and join that to the address table.

  Eg

  CREATE TABLE #TEMP_BORR

  (borr1fico varchar(50) not null,

  borr2fico varchar(50) null,

  borr3fico varchar(50) null)

   

  INSERT INTO #TEMP_BORR

       SELECT ... (as you had above)

   

  SELECT

  TB.borr1fico,

  TB.borr2fico,

  TB.borr3fico,

  AD1.street as borr1street,

  AD1.city as borr1city,

  AD2.street as borr2street,

  AD2.city as borr2city,

  AD3.street as borr3street,

  AD3.city as borr3city

  from #TEMP_BORR TB

  left outer join ADDRESS AD1 on TB.borr1fico = AD1.fico

  left outer join ADDRESS AD2 on TB.borr2fico = AD2.fico

  left outer join ADDRESS AD3 on TB.borr3fico = AD3.fico

  -- and finally drop the temporary table

  DROP TABLE #TEMP_BORR

   

  Hope that helps.

  Don.

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  June 7, 2004 at 6:50 am

  #509209

  select

  BORR.fico as borr1fico,

  BORR2.fico as borr2fico,

  BORR3.fico as borr3fico,

  a1.street as borr1street,

  a1.city as borr1city,

  a1.state as borr1state,

  a2.street as borr2street,

  a2.city as borr2city,

  a2.state as borr2state,

  a3.street as borr3street,

  a3.city as borr3city,

  a3.state as borr3state

  from tblloan EX with (nolock)

  inner join borrower BORR with (nolock) on BORR.Id = EX.ApplId

  left outer join address a1 on a1.Id = BORR.Id

  left outer join borrower BORR2 with (nolock) on BORR2.Borr_Id = EX.LoanApplId and BORR2.Borr_Id = '2'

  left outer join address a2 on a2.Id = BORR2.Id

  left outer join borrower BORR3 with (nolock) on BORR3.Borr_Id = EX.LoanApplId and BORR3.Borr_Id = '3'

  left outer join address a3 on a3.Id = BORR3.Id

  where EX.ApplId = '1234567890'

  and BORR.Borr_Id = '1'

  If this not right, can you post the ddl of the tables.

  Far away is close at hand in the images of elsewhere.
  Anon.