SQL Help

Question

SQL Help

Ian-232454

SSC Journeyman

Points: 92
More actions
December 9, 2008 at 9:33 am

#154999

I have DB that lists products and the categories in which they belong. This is done via a products table, Categories table and ProductCategories table. Which is fine as it allows products to be part of any number of Categories/Sub Categories. The basic set up is as follows
Products
------------------
ProductId
PartNumber
Categories
------------------
CategoryID
CategoryName
ParentCategoryID
ProductCategories
-------------------
ProductID
CategoryID
This works well however a change in application only allows for products to be placed in a single category and I need to extract the categories as such for import to the new DB
So in the current DB i would query and get something like this
item | Category | CategoryID |ParentCategoryID
partA | Alpha | 1 | 0
partA | AlphaSub | 15 | 1
partB | Beta | 2 | 2
partB | BetaSub | 16 | 5
What I am looking to do is write this to another table to look like this
Item | Category | SubCategory
PartA | Alpha | AlphaSub
PartB | Beta | BetaSub
Also note that in the original database some products may only have a Category and no subCategory. And some products can be listed in multiple Categories, ( PartC could be part of category Alpha and Beta)
I am at a complete loss on how to accomplish this and any insight would be deeply appreciated

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Ian, could you please set up some sample data for each of the three tables as indicated in the link below? You will also need to provide a table of expected results from the sample data.

Million-dollar question - will there only ever be two levels of category, i.e. category and sub-category?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 2

Sorry I did not see that page you linked, but very helpful. I will get that information out soon.

thank you Chris

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Ian (12/9/2008)
Sorry I did not see that page you linked, but very helpful. I will get that information out soon.
thank you Chris

You're welcome Ian, and thanks for taking the trouble to do this. There are one or two excellent methods for doing what you ask - which one will depend upon your data and your answers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 4

The product table contains a lot of records roughly 2800 and the productCategory table has roughly 3850 - should I include all the date?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ian (12/9/2008)
The product table contains a lot of records roughly 2800 and the productCategory table has roughly 3850 - should I include all the date?

No, twenty or so should be sufficient!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

noeld SSC Guru Points: 96590 More actions · Answer 6

You should post a "sample" of the data 4-6 rows per table is probably enough.

* Noel

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 7

Ok here is some sample data - simplified of course.

use tempInstantStore

IF OBJECT_ID('tempDB..product','U') IS NOT NULL

DROP TABLE product

CREATE TABLE [dbo].[product](

[ProductID] [int] NOT NULL,

[ManufacturerPartNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED

(

[ProductID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

IF OBJECT_ID('tempDP..category','U') IS NOT NULL

DROP TABLE category

CREATE TABLE [dbo].[Category](

[categoryID] [int] NOT NULL,

[Name] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ParentCategoryID] [int] NULL,

CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED

(

[categoryID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

IF OBJECT_ID('tempDP..productCategory','U') IS NOT NULL

DROP TABLE productCategory

CREATE TABLE [dbo].[ProductCategory](

[ProductID] [int] NULL,

[CategoryID] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO product (productID, manufacturerPartNumber)

select'1','Alpha'union all

select'2','Beta'union all

select'3','Gamma'union all

select'4','Delta'union all

select'5','Epsilon'union all

select'7','Zeta'union all

select'10','Zeta'

INSERT INTO productCategory (productID, CategoryID)

select'1','4'union all

select'1','8'union all

select'1','6'union all

select'2','4'union all

select'2','7'union all

select'3','1'union all

select'3','9'union all

select'4','1'union all

select'4','10'union all

select'5','2'union all

select'5','12'union all

select'7','13'union all

select'10','5'

INSERT INTO category (CategoryID, [name], parentCategoryID)

select'1','Cats','0'union all

select'2','Fish','0'union all

select'4','Dogs','0'union all

select'5','Other','0'union all

select'6','Small','4'union all

select'7','Retriever','4'union all

select'8','Toy Poodle','4'union all

select'9','Tabby','1'union all

select'10','Tuxedo','1'union all

select'11','Calico','1'union all

select'12','Small','2'union all

select'13','Medium','2'union all

select'14','Large','2'

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 8

Assumes a maximum of two levels of category

select p.manufacturerPartNumber as item,

c.name as Category,

subc.name as SubCategory

from product p

inner join productCategory pc on pc.productID=p.productID

inner join category c on c.CategoryID=pc.CategoryID and c.parentCategoryID=0

left outer join category subc on subc.parentCategoryID=c.CategoryID

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 9

Actually, the current DB design allows for multiple sub cats, so you could have Category/sub/subsub/subsubsub and so on, but the data I am using only goes to a sub category level, so you can assume that.

Ian-232454 SSC Journeyman Points: 92 More actions · Answer 10

Anyone have any suggestions on this? I am at a loss.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Ian (12/10/2008)
Actually, the current DB design allows for multiple sub cats, so you could have Category/sub/subsub/subsubsub and so on, but the data I am using only goes to a sub category level, so you can assume that.

Here's a link[/url] to an article which describes the use of CTE's to perform node recursion in SS2K5. Searching on "node recursion" will bring up quite a few others.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden