Solved

SQL Query Help

Posted on 2013-01-06
14
197 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
0
Comment
Question by:jbridges9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38749850
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
0
 

Author Comment

by:jbridges9
ID: 38749870
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?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38749940
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.
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 19

Expert Comment

by:Rimvis
ID: 38750347
Hi jbridges9,

Could you please post your table structure?
0
 

Author Comment

by:jbridges9
ID: 38752388
What is the best way to get what you need? Just type out the columns in each?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38752578
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
0
 

Author Comment

by:jbridges9
ID: 38753108
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!

--
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 38753706
Is this what you want?

SELECT sn1.[item_id], sn1.[serialnumbers_no] AS [assembly SN], 
	sn2.[item_id] AS [attached id/component], sn2.[serialnumbers_no] AS [attached id/component]
FROM [mw_serialassembly] AS a 
	INNER JOIN [mw_serialnumbers] AS sn1 ON a.[serialnumbers_id] = sn1.[serialnumbers_id] 
	INNER JOIN [mw_serialnumbers] AS sn2 ON a.[serialnumbers_attachedid] = sn2.[serialnumbers_id]

Open in new window

0
 

Author Comment

by:jbridges9
ID: 38753736
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!
0
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 500 total points
ID: 38753756
Like this?
SELECT i1.[item_no], sn1.[serialnumbers_no] AS [assembly SN], 
	i2.[item_no] AS [attached no/component], sn2.[serialnumbers_no] AS [attached id/component]
FROM [mw_serialassembly] AS a 
	INNER JOIN [mw_serialnumbers] AS sn1 ON a.[serialnumbers_id] = sn1.[serialnumbers_id] 
	INNER JOIN [mw_item] AS i1 ON sn1.[item_id] = i1.[item_id]
	INNER JOIN [mw_serialnumbers] AS sn2 ON a.[serialnumbers_attachedid] = sn2.[serialnumbers_id]
	INNER JOIN [mw_item] AS i2 ON sn2.[item_id] = i2.[item_id]

Open in new window


You just add another pair of JOINS to [mw_serialnumbers] table
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38756784
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.
0
 

Author Closing Comment

by:jbridges9
ID: 38757277
Excellent help!
0
 

Author Comment

by:jbridges9
ID: 38757669
One more thing.. I need to get the value of serialnumbers_status for each serialnumbers_no. serialnumbers_status is in the mw_serialnumbers table
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 38757987
You will have to add  sn1.[serialnumbers_status] and sn2. [serialnumbers_status] to you SELECT statement
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question