SQL Query Help

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;

Select allOpen in new window

This give results like this which I think is close to what you want

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.

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!

--

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!

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