I need some help writing a query in SQL. The purpose of the query is to list which of our vehicles are compatible with each part in our database.
Here are the tables involved and the relevant columns:
-=Parts=- PartNumber Notes
-=WoParts=- [A.K.A. Parts Used on Work Orders] RecordID (PK) PartNumber
-=WorkOrders=- RecordID (PK) VehKey (FK)
-=Vehicles=- RecordID (PK) vehicle VIN
I m trying to get a query to
1. Go through Parts.PartNumber one row at a time, 2. Running a query for all WoPart.RecordID s where Parts.PartNumber = WoPart.PartNumber, 3. Take 2 and run a query for all WorkOrders.RecordID = WoPart.RecordID, 4. Take WorkOrders.VehKey from the row of each matching WorkOrders.RecordID, displaying only distinct VehKeys, 5. Take 4 and query all Vehicles.VIN where Vehicle.RecordID = WorkOrders.VehKey, sorted by vehicle, 6. Take 5 , go back to the Parts.Notes and update the relevant section of the part s notes field.
|