We help IT Professionals succeed at work.

SQL Query Help

jbridges9
jbridges9 asked
on
14 Comments
2 Solutions
215 Views
Last Modified: 2013-01-09
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
Comment
Watch Question

View Solutions Only

Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
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
BOM_example_results
jbridges9

Author

Commented:
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?
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
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.
Rimvis

Commented:
Hi jbridges9,

Could you please post your table structure?
jbridges9

Author

Commented:
What is the best way to get what you need? Just type out the columns in each?
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
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.
Script_Table_option
jbridges9

Author

Commented:
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!

--
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jbridges9

Author

Commented:
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!
Rimvis
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Chris LuttrellSenior Database Architect
CERTIFIED EXPERT

Commented:
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.
jbridges9

Author

Commented:
Excellent help!
jbridges9

Author

Commented:
One more thing.. I need to get the value of serialnumbers_status for each serialnumbers_no. serialnumbers_status is in the mw_serialnumbers table
Rimvis

Commented:
You will have to add  sn1.[serialnumbers_status] and sn2. [serialnumbers_status] to you SELECT statement

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.

View Solution