I am having difficulty creating a nested query for my VB.NET application.
--Attached is my Database Design for reference (ignore cruddy table names).
My main focus is on the Part and Part_Pick tables. The Part Table lists all generic information for a certain part in inventory. The Part_Pick table is a reference table used to pick "Slave" (Master_Part_Ref_Id) parts for a corresponding "Master" (Master_Part_Id) part.
Master_Part_Ref_Id will be the field that will help pull "Slave" part information from the Part Table.
I want my application to use a Part.Part_Nbr (application level input) to identify a Master_Part_Id ("Master") to pull the corresponding "Slave" part information from the Part table.
This is what I have so far though it is just a test to check for part information. I may add an invisible text box to store the Master_Part_Id chosen for the "Master" part to pull all "Slave" parts.
FROM BoM.dbo.Part, BoM.dbo.Part_Type, BoM.dbo.Part_Subtype, BoM.dbo.Part_Type_Subtype_Xref, BoM.dbo.Part_Pick
WHERE Part.Part_Type_Xref_Id = Part_Type_Subtype_Xref.Part_Type_Xref_Id
AND Part_Type_Subtype_Xref.Part_Type_Id = Part_Type.Part_Type_Id
AND Part_Type_Subtype_Xref.Part_Subtype_Id = Part_Subtype.Part_Subtype_Id
AND Part_Pick.Master_Part_Ref_Id = Part.Master_Part_ID