Name:  DB Diagram.jpg
Views: 2861
Size:  77.8 KBI 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.


Part_Pick_Xref_Id, Master_Part_ID, Master_Part_Ref_Id,
1, 232, 599,
2, 232, 340,
3, 232, 218,
4, 232, 238,
5, 235, 600,
6, 235, 340,

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.
Select * 
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
Thank you for any insight you may offer.