Refining join query results

[DJT1]

Hi,

I'm trying to run a query that will show all the Part Numbers from a Parts table along with their corresponding Model Number (It's possible be multiple Model No's for some parts). I have one table (ModelLookup) with a Model Number and Model ID, ModelPartLink with a Model Id and Part ID and Part with Part ID and Part Number. Finally I have Part Number joined to another table (MaterialNumbers_Excel) with a Material No field that matches the Part No's. At the moment I have joined the tables by the common fields and have set the query properties to not show duplicates.

I currently get each Part Number returned against every Model No.

SELECT DISTINCT MaterialNumbers_Excel.[Old material no], Part_7_2.PartNumber, Part_7_2.PartName, Part_7_2.PartType, Part_7_2.MSRP, ModelLookUp.ModelNumber
FROM ((ModelLookUp INNER JOIN ModelPartLink_7_2 ON ModelLookUp.ModelID = ModelPartLink_7_2.ModelID) INNER JOIN Part_7_2 ON ModelPartLink_7_2.PartID = Part_7_2.PartID) INNER JOIN MaterialNumbers_Excel ON Part_7_2.PartNumber = MaterialNumbers_Excel.[Old material no];

I'm sure I'm missing something easy but I just can'rt see it ATM.

Thanks.

 

[rac2]

SELECT DISTINCT MaterialNumbers_Excel.[Old material no],  
   Part_7_2.PartNumber,
   Part_7_2.PartName,
   Part_7_2.PartType,
   Part_7_2.MSRP,
   ModelLookUp.ModelNumber

FROM ModelLookUp
INNER JOIN ModelPartLink_7_2 ON ModelLookUp.ModelID = ModelPartLink_7_2.ModelID
INNER JOIN Part_7_2 ON ModelPartLink_7_2.PartID = Part_7_2.PartID
INNER JOIN MaterialNumbers_Excel ON Part_7_2.PartNumber = MaterialNumbers_Excel.[Old material no];

Does ModelPartLink_7_2 have the right number of rows, that is, for each part the appropriate models, for all of the parts? In other words, it has exactly the rows you wish to see in the result? If so, then use it as the starting point in the query. The other tables in a sense merely describe what is in this table; place them in the JOIN clauses.

And does ModelLookUp have exactly one row for each model?

And does Part_7_2 have exactly one row for each part?

My guess is yes to the above, but always worth checking via simple queries to rule out bad and unexpected data.

That leaves MaterialNumbers_Excel as a likely suspect. First because it appears to be imported data; and from an old source; and Excel tables sometimes get messy. It is possible that the spreadsheet source also had rows for every model and part, like the ModelPartLink_7_2 table? That means there are multiple rows for each part in it so the JOIN gets multiple rows for each part. And the JOIN already has multiple rows for each part.

SELECT DISTINCT MaterialNumbers_Excel.[Old material no],  
   Part_7_2.PartNumber,
   Part_7_2.PartName,
   Part_7_2.PartType,
   Part_7_2.MSRP,
   ModelLookUp.ModelNumber

FROM ModelPartLink_7_2
INNER JOIN ModelLookUp ON ModelLookUp.ModelID = ModelPartLink_7_2.ModelID
INNER JOIN Part_7_2 ON ModelPartLink_7_2.PartID = Part_7_2.PartID
INNER JOIN MaterialNumbers_Excel ON Part_7_2.PartNumber = MaterialNumbers_Excel.[Old material no];

Bottom line the query looks copacetic; must be bad data.


PS. There is seldom any need for the DISTINCT qualifier, a properly constructed query from a well designed database with well behaved data almost never requires it.

 

[DJT1]

Thanks for the reply rac2,

The data I have is actually pretty messy. I first wanted to make sure my query was sound.

ModelLookup has 48103 records and has no duplicates for Model ID.

ModelLookup is a common model list. ModelPartLink and Part have 4 volumes each (they are divided up into volumes as they are distributed on multiple cd's):

Part_7_1
Part_7_2
Part_7_3
Part_7d

ModelPartLink_7_1
ModelPartLink_7_2
ModelPartLink_7_3
ModelPartLink_7d

Part_7_2 has 95080 records and no duplicates for Part ID.

So there are multiple parts per model.

The real problem lies with ModelPartLink_7_2 which has 2767128 records and does have duplicate combinations of Model ID and Part ID.

So what I'll try from here is to query the ModelPartLink_7_2 table and remove duplicates then try and use that data.