Link to home
Create AccountLog in
Avatar of jbridges9
jbridges9Flag for United States of America

asked on

SQL Query Help

I was hoping someone could help me with a SQL query. I am running MS SQL Server 2008. I've tried to explain what I'm trying to do. I don't know if I explain well enough to make sense.

Thanks.

For each mw_serialassembly , We need to loop thru the mw_serialnumbers table using the serialnumbers_attachedid and list out the Bill of Materials (BOM) for this assembly.

So for instance, the DB structure looks like this:

serialnumbers_id             serialnumbers_attachedid
31066                                    15343
31066                                    16106
31066                                    31071
31070                                    16049
31070                                    31082
31070                                    31088

For serialnumbers_id, there will be an entry for the completed assembly item with the serialnumbers_attachedid being the “components” of the BOM. We need to look at the mw_serialnumbers table for each ID and list out the serialnumbers_no for that ID, the item_id for that serialnumbers_no , and then do the same for each “attached_id”  so that we have something that looks like this:

Item_id    serialnumbers_no (assembly SN), item_id (attached id/component) serialnujmbers_no (attached id/component)
10246       HD0000062                                                       10249                                                    NLS000123
10246       HD0000062                                                       10250                                                    ENC000123
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

You did not describe your tables and structure quite well enough to use in an example but here is a typical BOM example I have used before to describe how to use a CTE to build a BOM.
CREATE TABLE Parts (PartNo INT IDENTITY(1000,1), PartName VARCHAR(50));
SET IDENTITY_INSERT Parts ON
INSERT INTO Parts (PartNo, PartName) VALUES (1, 'Nuts'); 
INSERT INTO Parts (PartNo, PartName) VALUES (2, 'Bolts'); 
INSERT INTO Parts (PartNo, PartName) VALUES (3, '4 Ft 2x2 Angle Iron'); 
INSERT INTO Parts (PartNo, PartName) VALUES (4, 'SubAssembly 1'); 
INSERT INTO Parts (PartNo, PartName) VALUES (5, 'SubAssembly 2'); 
INSERT INTO Parts (PartNo, PartName) VALUES (6, 'Final Assembly'); 
SET IDENTITY_INSERT Parts OFF

CREATE TABLE BOM (AssemblyId INT IDENTITY(1,1), AssemblyPartNo INT NOT NULL, ComponentPartNo INT NOT NULL, Qty INT NOT NULL);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (6,5,2);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (6,4,1);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (6,1,4);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (6,2,4);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (5,3,2);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (5,1,2);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (5,2,2);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (4,3,4);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (4,1,8);
INSERT INTO BOM (AssemblyPartNo, ComponentPartNo, Qty) VALUES (4,2,8);

WITH BomExpl(AssemblyPartNo, ComponentPartNo, Qty, PartName, ComponentLevel) AS
(
SELECT b.AssemblyPartNo
,b.ComponentPartNo
,b.Qty
, PartName
,0 AS ComponentLevel
FROM dbo.BOM AS b
INNER JOIN Parts P ON b.AssemblyPartNo = P.PartNo
WHERE P.PartName = 'Final Assembly' 
UNION ALL
SELECT bom.AssemblyPartNo
,bom.ComponentPartNo
,bom.Qty
, Parts.PartName
,ComponentLevel + 1
FROM dbo.BOM AS bom 
INNER JOIN Parts ON bom.AssemblyPartNo = Parts.PartNo
INNER JOIN BomExpl AS p
ON bom.AssemblyPartNo = p.ComponentPartNo
)
SELECT d.AssemblyPartNo
,d.ComponentPartNo
,d.Qty
, PartName
,ComponentLevel 
FROM BomExpl AS d
ORDER BY ComponentLevel, AssemblyPartNo, ComponentPartNo


DROP TABLE BOM;
DROP TABLE Parts;

Open in new window

This give results like this which I think is close to what you want
User generated image
Avatar of jbridges9

ASKER

I have little SQL knowledge so I'll need some hand holding. Can you tell me what you need for me to describe in order to help me more?
The table names, column names and data types and some sample data for them that I can see how your data really works together.  The small amount of data above does not all go together, your DB structure data does not match your desired output and I can't tell what table and columns all those values come from.
Hi jbridges9,

Could you please post your table structure?
What is the best way to get what you need? Just type out the columns in each?
Can you get the create table statements like in my example above?  You can right click on a table in Management Studio and script it out to the clipboard and send them.  We would need any tables that have data you want in your output.
User generated image
USE [mwdb]
GO

/****** Object:  Table [dbo].[mw_serialassembly]    Script Date: 01/07/2013 19:06:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mw_serialassembly](
      [serialnumbers_id] [int] NOT NULL,
      [serialnumbers_attachedid] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
      [serialnumbers_id] ASC,
      [serialnumbers_attachedid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[mw_serialassembly]  WITH CHECK ADD FOREIGN KEY([serialnumbers_id])
REFERENCES [dbo].[mw_serialnumbers] ([serialnumbers_id])
GO

ALTER TABLE [dbo].[mw_serialassembly]  WITH CHECK ADD FOREIGN KEY([serialnumbers_attachedid])
REFERENCES [dbo].[mw_serialnumbers] ([serialnumbers_id])
GO

--

USE [mwdb]
GO

/****** Object:  Table [dbo].[mw_serialnumbers]    Script Date: 01/07/2013 19:07:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[mw_serialnumbers](
      [serialnumbers_id] [int] IDENTITY(10000,1) NOT NULL,
      [serialnumbers_no] [varchar](50) NULL,
      [serialnumbers_issold] [bit] NULL,
      [serialnumbers_isactive] [bit] NULL,
      [item_id] [int] NOT NULL,
      [strans_id] [int] NULL,
      [po_id] [int] NULL,
      [serialnumbers_polineid] [int] NULL,
      [serialnumbers_stranslineid] [int] NULL,
      [serialnumbers_createdate] [datetime] NULL,
      [serialnumbers_modifydate] [datetime] NULL,
      [loc_id] [int] NULL,
      [serialnumbers_status] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
      [serialnumbers_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[mw_serialnumbers]  WITH CHECK ADD FOREIGN KEY([item_id])
REFERENCES [dbo].[mw_item] ([item_id])
GO

ALTER TABLE [dbo].[mw_serialnumbers]  WITH CHECK ADD FOREIGN KEY([loc_id])
REFERENCES [dbo].[mw_location] ([loc_id])
GO

ALTER TABLE [dbo].[mw_serialnumbers]  WITH CHECK ADD FOREIGN KEY([strans_id])
REFERENCES [dbo].[mw_salestransaction] ([strans_id])
GO
--


I think this is what you need. T hanks!

--
ASKER CERTIFIED SOLUTION
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Yes!! Exactly. Is it possible to take this one step further and for each item_id , loop through the mw_item table and grab the value of item_no and store the item_no value in the item_id ?

Thanks!
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
When you say "store the item_no value in the item_id" are you wanting to update the table and change the ID or return the item_no value instead of item_id like the comment above?

Also, test your query with more than 1 level deep in you BOM.  The CTE (common-table-expression) in my BOM example above is the way to structure the query to Recurse a multi-level join.
Excellent help!
One more thing.. I need to get the value of serialnumbers_status for each serialnumbers_no. serialnumbers_status is in the mw_serialnumbers table
You will have to add  sn1.[serialnumbers_status] and sn2. [serialnumbers_status] to you SELECT statement