join problem

• Nuts

  SSCrazy

  Points: 2547

  More actions

  September 21, 2006 at 3:53 pm

  #114587

  Hi guys

  I need to perform a join on two tables which have got 1 common field named 'Provider'.

  The first table is the master table and the second table has got detailed description of the 'provider'

  I want to create a new table with all the 'providers' listed in the first table and details of the providers from second table but for only those providers listed in the master table .

  The problem is there is no unique field in the master table.

  how do i do this?

   

  thanks

  mita

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  September 21, 2006 at 4:28 pm

  #661878

  So there's no primary key on master table?

  You could do a distinct from master and perhaps use an aggregate from the child table with a LEFT JOIN.

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 21, 2006 at 4:58 pm

  #661883

  The query below assumes Provider is the unique key on Master table. If Not u will get duplicate information.

  Select mastertable.Provider,mastertable.col1,mastertable.col2,

  mastertable.col3,...childtable.col1,Childtable.col2...

  from mastertable LEFT OUTER JOIN childtable on mastertable.Provider = childtable.Provider

  Thanks

  Sreejith