join 3 tbls

• mjschwenger

  SSCarpal Tunnel

  Points: 4019

  More actions

  March 24, 2004 at 2:28 pm

  #107806

  I have a case like this:

  table a - has one filed with statusID

  table b - has one filed with statusID

  c - status code tbl - statusID(int,4), status Decription (char, 20)

  When I want to pull the description for the statuses I have in "a" and "b" for a particular custID (they will be always different) I could use:

  select * from a left join b on a.pk_a = b.fk_a left join c on a.fk_c = c.pk_c left join c as d on b.fk_c = d.pk_c

  where a.custid = "xxx"

  But I think that there is more elegant way of doing that.

  Any suggestions?

  Thanks a lot.

  MJ

• Antares686

  SSC Guru

  Points: 125444

  More actions

  March 24, 2004 at 2:34 pm

  #500328

  Try this

   

  select * from

   a

  left join

   b

  on

   a.pk_a = b.fk_a

  left join

   c

  on

   a.fk_c = c.pk_c OR

   b.fk_c = c.pk_c

  where

   a.custid = "xxx"