You’re offline. This is a read only version of the page.
50
Good morning,
I'm looking for some assistance with an SQL query. I'm attempting to show the BOM header, plus all of the individual line items within the BOM. I've *almost* got the result I'm looking for, however I can't seem to be able to show the individual item name, it is showing the BOM header name. I'm sure this is something very simple but I can't figure out if I'm missing a relation table in order to be able to show that information. My code so far is as follows;
SELECT ITEM.ITEMID,
Detail.NAME AS BOMHeader,
B.ITEMID AS ItemNo,
Detail.NAME AS ItemDescription
FROM BOMTABLE AS Detail
JOIN BOM AS B
ON B.BOMID = Detail.BOMID
JOIN InventTable AS Item
ON Detail.NAME = Item.NAMEALIAS
WHERE ITEM.ITEMID = 'xxx'
ORDER BY Item.ITEMID
Now I know that it is not best practice to join tables on a name field, however there is no other column that I can see that can be used to join, which is what is making me think I'm potentially missing a related table to give me the detail that I want. Any help will be massively appreciated.
Thanks
Rhiannon
*This post is locked for comments
I have the same question (0)Hi Crispin, thank you for taking the time to respond.
The Item.ItemID is different to B.ItemID - Example,
B.ItemID = Six digit numeric code
Item.ItemID = Alphanumeric defined by the business, no digit limit
The reason for ORDER BY Item.ITEMID is because I had an example whereby Item.ITEMID was ABC_123 and one that was ABC_123-1, ordering by kept them in order.
Thanks
Rhiannon
Share
Report
All responses (
Answers (
