OUTER JOIN - Help, I''m stumped

• David L. Collison

  Newbie

  Points: 2

  More actions

  September 15, 2006 at 1:18 pm

  #173947

  Good Afternoon,

  Forgive the lengthy discourse, but I want to provide as much information as possible so that someone will be able to help me out.  This question concerns getting an outer join to work the way that I expect it to:

  Table A - tblSOPType

  Id     Description

  1      Startup SOP

  2      Shutdown SOP

  3      Emergency Shutdown

  4      Post Emergency Startup

   

  Table B - tblSOPS

  ID     tblEquipment_ID      tblSOPType_ID

  1      1                          1

  2      1                          2

  3      2                          1

  3      2                          1

   

  What I want to do is produce a query that gives me the following results:

  sqlQuery Results

  tblSOPS_ID     tblEquipment_ID    tblSOPType_ID

  1                  1                        1

  1                  1                        2

  null               1                        3

  null               1                        3

   

  I have attempted the following query:

  SELECT

   tblSOP_Type.Id,  tblSOPS.tblEquipment_id, tblSOPS.tblSOPType_id

  FROM

  tblSOP_Type

  LEFT

  OUTER JOIN tblSOPS ON tblSOPS.tblSOPType_id = tblSOP_Type.id

  WHERE

   tblEquipment_ID = 1

  I only get the following records:

  1                  1                        1

  1                  1                        2

   

  Any suggestions on how to get the null records where there was no match?

  Thank you ...

   

  dlcollison

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 15, 2006 at 2:10 pm

  #660822

  Try this query:

  SELECT

      tblSOP_Type.Id,

      tblSOPS.tblEquipment_id,

      tblSOPS.tblSOPType_id

  FROM

      tblSOP_Type

      LEFT OUTER JOIN tblSOPS

          ON (tblSOPS.tblSOPType_id = tblSOP_Type.id

              tblEquipment_ID = 1)

  hth!

  

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 15, 2006 at 2:17 pm

  #660823

  I hope u are trying this:

   

  select * from tblSOP_Type

  LEFT OUTER JOIN tblSOPS ON tblSOP_Type.id = tblSOPS.tblSOPType_id

  thanks

  Sreejith

• David L. Collison

  Newbie

  Points: 2

  More actions

  September 15, 2006 at 2:19 pm

  #660825

  Journeyman ...

  You DA man!!!!!

  Worked like a charm

  Thanks ...

  dlcollison