Link to home
Start Free TrialLog in
Avatar of lividicus
lividicus

asked on

Query: Complicated Junction Table

How can I populate a junction Table when the data comes from multiple other tables.  
I attached a diagram of the tables and relationships I am using.

What i have is an empty Pack_J_Price table.  I have data in the surrounding tables.

I need to insert the following in the junction table.
1-INSERT the PARTID,  PartPackageID from the PartPackage Table
2- INSERT the JobID, PackageID from the PackageJob Table
3-INSERT the PartsPricingID where the Part ID found in the Package table is found WHERE the PartID's pdate is most recent.


Could you narate what is happening as you go s i understand whats going on.



diagram1.jpg
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lividicus
lividicus

ASKER

Angel thank you for your response.  I just want to make sure that if i do attached snippet  that it will update any chages in a populated table is that correct?
INSERT INTO Pack_J_Price (PackageID, PartID, PartsPricingId, Jobid)
SELECT pp.packageID, pp.PartID, pr.PartsPricingId, pj.jobid
  FROM PartPackage pp
  JOIN PartsPricing pr 
    ON pr.PartID = pp.PartID
   AND pr.pDate = (SELECT MAX (i.pdate) FROM partspricing i WHERE i.partID = pp.Partid )
  JOIN packageJob pj  
    ON pj.PackageID = pp.PackageID
 
WHERE NOT EXISTS (SELECT 1
FROM Pack_J_Price
WHERE PackageID = pp.PackageID
AND PartID = pp.PartID)
AND JobID = pj.jobID
AND PartsPricingID = pr.PartsPricingID

Open in new window

>that it will update any chages in a populated table is that correct?
it will not "update" anything. if you want "updates", you have to write an UPDATE statement :)
oops i meant it will add entries that are not already i there?
Thanks it works!