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
Solved

SQL Query Help

Posted on 2013-01-06
14
195 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
  • 6
  • 4
  • 4
14 Comments
 
LVL 26

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 26

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 26

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 26

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.

856 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