CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012
    Posts
    46

    [RESOLVED] [MSSQL Server 2005] Help with Query

    Name:  DB Diagram.jpg
Views: 3769
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.

    ex:

    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.
    Code:
    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.
    --Seth

  2. #2
    Join Date
    Jul 2012
    Posts
    46

    Re: [RESOLVED] [MSSQL Server 2005] Help with Query

    I was able to make a few changes on the application level to force a similar query to work
    Code:
    --Reference Part List Data Table
    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
    AND Part_Pick.Master_Part_Id = '" & txtMotorMasterId.text & "'
    AND Part_Subtype.Part_Subtype_Name = 'Motor Protection'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured