Dividing a problem into parts and can't get the parts back together

Last Post 06 May 2010 09:41 AM by tcarnes. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tcarnes
New Member
New Member

--
06 May 2010 09:41 AM
I have three tables in a Master/Detail/Detail relationship. They are titled Label / Product / ProductPart. One Label can have one or more Products and each Product can have one or more ProductParts.

The ProductParts have two attributes: Category and Application Number (productpart_category, productpart_app_no). Usually (95% of the time), the Category and Appliction Number is the same for all the ProductParts for a given Label. This makes it easy to return a result table (after a search of the 20,000 labels) like this:

Label Id | Label Name (taken from Label table) | Category | Appl. No

Since a Label (one of the 5%) can have 20 or more different Categories or Application Numbers, the specs are that if there is more than one, I just return one appended with ", ..." (a comma, a space, and an ellipsis).

When I'm stumped with a problem, I usually try to divide it into small parts, get the answers on the smaller parts and then put it all back together again. I find with this problem I can get the answers I want in the parts, but can't figure out how to put it all back together again.

By using Union, I am able to create two result sets that handle returning single or multiple Categories and AppNos:

Select label_id, Max(productpart_app_no) as AppNo
    from product p inner join productpart pp ON p.product_id = pp.product_id
    group by label_id
    having count(distinct productpart_app_no) = 1
Union All
Select label_id, Max(productpart_app_no) + ', ...' as AppNo
    from product p inner join productpart pp ON p.product_id = pp.product_id
    group by label_id
    having count(distinct productpart_app_no) > 1
order by label_id

This returns:

label_id    AppNo
-----------  ------------
1              C-12345
2              C-23456
3              C-34567
4              C-67890, ...

I can do the same thing with the categories:

Select label_id, Max(productpart_category) as Category
    from product p inner join productpart pp ON p.product_id = pp.product_id
    group by label_id
    having count(distinct productpart_category) = 1
Union All
Select label_id, Max(productpart_category) + ', ...' as Category
    from product p inner join productpart pp ON p.product_id = pp.product_id
    group by label_id
    having count(distinct productpart_category) > 1
order by label_id

Which returns:

label_id    Category
-----------  ------------
1              Cat1
2              Cat2
3              Cat4, ...
4              Cat5

My problem is when I try to put this all together to return one result set like this:

label_id   label_name    AppNo           Category
----------  ----------------   ---------------  ----------------
1              Label1           C-12345        Cat1
2              Label2           C-23456        Cat2
3              Label3           C-34567        Cat4, ...
4              Label4           C-67890, ...   Cat5

Would someone please help me to put this all together?

Terry

The following SQL will create my three test tables:

/****** Object:  ForeignKey [FK_Product_Label]    Script Date: 05/06/2010 13:35:55 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Product_Label]') AND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product] DROP CONSTRAINT [FK_Product_Label]
GO
/****** Object:  ForeignKey [FK_ProductPart_Product]    Script Date: 05/06/2010 13:35:55 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductPart_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductPart]'))
ALTER TABLE [dbo].[ProductPart] DROP CONSTRAINT [FK_ProductPart_Product]
GO
/****** Object:  Table [dbo].[ProductPart]    Script Date: 05/06/2010 13:35:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPart]') AND type in (N'U'))
DROP TABLE [dbo].[ProductPart]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 05/06/2010 13:35:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
DROP TABLE [dbo].[Product]
GO
/****** Object:  Table [dbo].[Label]    Script Date: 05/06/2010 13:35:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Label]') AND type in (N'U'))
DROP TABLE [dbo].[Label]
GO
/****** Object:  Table [dbo].[Label]    Script Date: 05/06/2010 13:35:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Label]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Label](
    [label_id] [int] NOT NULL,
    [label_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Label] PRIMARY KEY CLUSTERED
(
    [label_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[Label] ([label_id], [label_name]) VALUES (1, N'Label1')
INSERT [dbo].[Label] ([label_id], [label_name]) VALUES (2, N'Label2')
INSERT [dbo].[Label] ([label_id], [label_name]) VALUES (3, N'Label3')
INSERT [dbo].[Label] ([label_id], [label_name]) VALUES (4, N'Label4')
/****** Object:  Table [dbo].[Product]    Script Date: 05/06/2010 13:35:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
    [product_id] [int] NOT NULL,
    [product_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [label_id] [int] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (1, N'Product1', 1)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (2, N'Product2', 2)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (3, N'Product3', 3)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (4, N'Product4', 3)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (5, N'Product5', 3)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (6, N'Product6', 3)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (7, N'Product7', 4)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (8, N'Product8', 4)
INSERT [dbo].[Product] ([product_id], [product_name], [label_id]) VALUES (9, N'Product9', 4)
/****** Object:  Table [dbo].[ProductPart]    Script Date: 05/06/2010 13:35:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductPart]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProductPart](
    [productpart_id] [int] NOT NULL,
    [productpart_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [productpart_category] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [productpart_app_no] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [product_id] [int] NOT NULL,
 CONSTRAINT [PK_ProductPart] PRIMARY KEY CLUSTERED
(
    [productpart_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (1, N'Part1', N'Cat1', N'C-12345', 1)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (2, N'Part2', N'Cat1', N'C-12345', 1)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (3, N'Part3', N'Cat2', N'C-23456', 2)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (4, N'Part4', N'Cat2', N'C-23456', 2)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (5, N'Part5', N'Cat3', N'C-34567', 3)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (6, N'Part6', N'Cat3', N'C-34567', 4)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (7, N'Part7', N'Cat4', N'C-34567', 5)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (8, N'Part8', N'Cat4', N'C-34567', 6)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (9, N'Part9', N'Cat5', N'C-45678', 7)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (10, N'Part10', N'Cat5', N'C-56789', 8)
INSERT [dbo].[ProductPart] ([productpart_id], [productpart_name], [productpart_category], [productpart_app_no], [product_id]) VALUES (11, N'Part11', N'Cat5', N'C-67890', 9)
/****** Object:  ForeignKey [FK_Product_Label]    Script Date: 05/06/2010 13:35:55 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Product_Label]') AND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Label] FOREIGN KEY([label_id])
REFERENCES [dbo].[Label] ([label_id])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Label]
GO
/****** Object:  ForeignKey [FK_ProductPart_Product]    Script Date: 05/06/2010 13:35:55 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProductPart_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProductPart]'))
ALTER TABLE [dbo].[ProductPart]  WITH CHECK ADD  CONSTRAINT [FK_ProductPart_Product] FOREIGN KEY([product_id])
REFERENCES [dbo].[Product] ([product_id])
GO
ALTER TABLE [dbo].[ProductPart] CHECK CONSTRAINT [FK_ProductPart_Product]
GO

You are not authorized to post a reply.

Acceptable Use Policy