[tody4me]

I need help with a small database (less than 500 lines / table) that I'm working on. I have a parts list that I'm breaking down by manufacturer, seller, model number, new / old number, etc. I need to have a query pull in all the information based on old number, some of the old numbers will not have new numbers, will not have manufacturer, will not have a brand name, etc, but I still want all of the old numbers to come in, and a <NULL> value assigned to the fields that are not relavent. I was trying to accomplish this with the use of left and right outer joins, but for some reason when I put it all together, I get an error about the order of the join and for some reason can't seem to figure out what's wrong. I have tried using other queries to pull in some of the data and piece it together and it still only returns 139 records, where I am guessing it should be closer to 300 if working correctly. Tables I have are
[OldPartNumber]
-----
OldPartID
ModelNo

[ModelNumber]
-----
ModelID
ModelName
ModelReplaced
ProductType
Dimensions

[NewPartNumber]
----
NewPartID
OldPartID

[SKUs]
----
SKUID
BrandID

[BrandName]
-----
BrandID
BrandName
IsRelavent

[ProductTypes]
-----
ProductTypeID
ProductTypeName

[XRef]
SKUID
OldPartID

[NewXRef]
NewPartID
OldPartID
....

What I want to do is join the data so that I can bring in Model Name, Model Replaced, Model Dimensions, Product Type Name, Brand Name, SKU#, and New Number.

SELECT ProductLines.ProdcutLineName, BrandName.BrandName, XRef.SKUNo, XRef.OldPartID, NewPartNumber.NewPartID, ModelNumbers.ModelName, ModelNumbers.ModelReplaced, ModelNumbers.Dimensions
FROM ((BrandName INNER JOIN SKUs ON BrandName.BrandNameID = SKUs.BrandID) INNER JOIN (ProductLines INNER JOIN (ModelNumbers INNER JOIN (OldPartNumber INNER JOIN XRef ON OldPartNumber.OldPartID = XRef.OldPartID) ON ModelNumbers.ModelID = OldPartNumber.OldPartName) ON ProductLines.ProductLineID = ModelNumbers.ProductLineID) ON SKUs.SKUNo = XRef.SKUNo) INNER JOIN NBXRef ON OldPartNumber.OldPartID = NewXRef.OldPartID
ORDER BY ProductLines.ProdcutLineName, XRef.SKUNo;

This returns 110 records, I modded it slightly by using 2 different queries for the XRef links, but that returned only 139 lines. I don't have the modification any more because when it failed I removed the query.

Thanks for your help.

[Tiny Grasshopper]

My SQL is a bit rusty but have you tried using the outer joins. As memory serves inner joins work by joining tables and ingoring the rows that have null valures in them. As i said my sql is rusty so i don't know if it will work. I don't have code with me but i will try to get it and post it later.

[tody4me]

Thank you for your reply ...
I did try to use the outer joins, that's where I was having the problem. I am trying to use outer joins (left join in this case) to bring in all old numbers, and link any information pertaining to the old number from the other tables. When I use more than one left (outer) join, I get an error stating that the joins are ambigious and that I would need to break them down into smaller queries. That's what I did to fix the problem with not being able to bring in all the data, but I wanted to just use outer joins instead of have 10 extra queries just to bring in the same result.

[gregoryH]

I was looking at your query and think that the order of some of the joins may be causing you some grief. So I tried to Model you DB using the query to help me identify what you are trying to do.

Part of the problem with SQL lies in the OEM's implementation of the elements of the language, making some of it not so portable

have a look at the attached PDF of the db design, clearly I am missing some of the table with no idea what the design was intended to do.

Attached File(s)

•  dicdbhelp1.pdf (13.35K)
  Number of downloads: 77