Left Outer join

• Chandru -734144

  Ten Centuries

  Points: 1040

  More actions

  July 16, 2008 at 1:38 am

  #191011

  Dear all

  I have a problem regarding the left outer join can any one help me on this.The following is the script

  create table #Department (DepID int NOT NULL PRIMARY KEY,

  DepName varchar(50) NOT NULL)

  go

  INSERT #Department (DepID, DepName)

  SELECT 1, 'Dep1' UNION ALL

  SELECT 2, 'Dep2' UNION ALL

  SELECT 3, 'Dep3' UNION ALL

  SELECT 4, 'Dep4'

  go

  create table #Department_Person (DepID int NOT NULL,

  PersonID int NOT NULL,

  AuthorityID int NOT NULL,

  PRIMARY KEY (DepID, PersonID))

  go

  INSERT #Department_Person (DepID, PersonID, AuthorityID)

  SELECT 1, 1, 1 union all

  SELECT 1, 2, 2 union all

  SELECT 1, 3, 2 union all

  SELECT 2, 4, 1 union all

  SELECT 2, 5, 2 union all

  SELECT 2, 6, 2 union all

  SELECT 3, 7, 2 union all

  SELECT 3, 8, 2

  go

  CREATE TABLE #Person(PersonID int NOT NULL PRIMARY KEY,

  FirstName varchar(50) NOT NULL,

  LastName varchar(50) NOT NULL)

  go

  INSERT #Person(PersonID, FirstName, LastName)

  SELECT 1, 'Andy', 'Ason' union all

  SELECT 2, 'Bernie', 'Bson' union all

  SELECT 3 , 'Ceasar', 'Cson' union all

  SELECT 4 , 'Dave', 'Dson' union all

  SELECT 5 , 'Eric', 'Eson' union all

  SELECT 6 , 'Freddy', 'Fson' union all

  SELECT 7 , 'Grant', 'Gson' union all

  SELECT 8 , 'Harry', 'Hson'

  go

  i need the o/p like this

  DepID DepName FirstName LastName

  1Dep1AndyAson

  1Dep1BernieBson

  1Dep1CeasarCson

  2Dep2DaveDson

  2Dep2EricEson

  2Dep2FreddyFson

  3Dep3GrantGson

  3Dep3HarryHson

  4 Dep4 Null Null

  But i get achive the result as required

  My Coding--

  select d.Depid,Depname,firstname,lastname

  from #department d left outer join

  #department_person dp on d.depid=dp.depid

  join #person p on p.personid=dp.personid

  Where im doing mistake can any one help me on this..

  Thanks.

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  July 16, 2008 at 2:00 am

  #843584

  Hi Chandru,

  Try this

  SELECT D.DepID,D.DepName,P.FirstName,P.LastName

  FROM #Department D

  LEFT OUTER JOIN #Department_Person DP ON D.DepID=DP.DepID

  LEFT OUTER JOIN #Person P ON P.PersonID=DP.PersonID

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• Chandru -734144

  Ten Centuries

  Points: 1040

  More actions

  July 16, 2008 at 2:08 am

  #843588

  Thanks Hari,

  I got the Required O/p thanks for your reply.

  Regards,

  Chandru.