can I select info from 2 tables into other table

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  January 15, 2004 at 9:03 am

  #162948

  Hi,

  I have 3 tables.

  < SCHEMA >

   

  tblMechanic

  mechanicId PK

  mechanicName 

   

  tblMechanicJobType

  mechanicId  FK refs tblMechanic.mechancic

  jobTypeId FK  refs  tblJobType.jobTypeId

   

  tblJobType

  jobTypeId PK

  jobTypeName

   

  <\SCHEMA>

   

  I have already populated both tblMechanic, and tblJobType.

  Is there any way that I can use a query to  populate tblMechanicJobType, with all the values from tblMechanic and tblJobType?

   

  Eg, if I have 2 mechanics:

  mechanicId    0    1

  mechanicName  jim  john

   

  ..and  2 job types

   

  jobTypeId     0        1

  jobTypeName   valet    wheelBalance

   

   

  Is there an sql statement that will populate tblMechanicJobType

  With all:

  1)    mechanicId’s

  2)    jobTypeId’s

   

  .. so that tblMechanicJobType would be as follows:

   

  tblMechanicJobType

  mechanicId  0  0  1  1

  jobTypeId   0  1  0  1

   

  I’ve been  fluting about with select INTO statements, but no joy.

  Can anyone advise?

   

  Cheers,

  yogiberr

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  January 15, 2004 at 9:18 am

  #490235

  INSERT INTO tblMechanicJobType (mechanicId, jobTypeId)

  SELECT Mech.mechanicId, JobT.jobTypeId

  FROM tblMechanic Mech

  CROSS JOIN tblJobType JobT

  [WHERE whatever]

  
  
  Once you understand the BITs, all the pieces come together

• yogiberr

  Hall of Fame

  Points: 3679

  More actions

  January 15, 2004 at 10:05 am

  #490248

  that's the one.

  cheers Thomas.

  yogi