May 31, 2012 at 7:21 am
Apologies I have just read the best practices and am currently re writing the table so its easier to read.:-)
Hi I am having a bit of trouble and wondered if anyone can point me in the right direction.
I have a table called categories with Category names, ID's and Parent id's
I am cross referencing a parts list table Called Parts with Part Number and CatId
I want to pull back the categories with the relevent part number to then send up to the web for my store, the problem is I get back the categories and know the parent but if the category is 3 levels down I don't get the top level category (ie one with a parent ID of 0)
does anyone have any suggestions on how to pull back all matched categories going all the way up the tree until it reaches parent id 0.
I hope this is clear.
Thanks for your help.
John.
--===== Create the test table with
CREATE TABLE [dbo].[CatIdPidName](
[categories_id] [smallint] NOT NULL,
[parent_id] [smallint] NULL,
[categories_name] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--===== Insert the test data into the test table
INSERT INTOCatIdPidName
(categories_id, parent_id, categories_name)
SELECT '2','0','Software', UNION ALL
SELECT '4','55','Database software', UNION ALL
SELECT '7','2','Operating systems', UNION ALL
SELECT '12','2','Office suites', UNION ALL
SELECT '14','2','Financial analysis software', UNION ALL
SELECT '15','2','Accounting software', UNION ALL
SELECT '21','2','Multimedia software', UNION ALL
SELECT '24','21','OCR software', UNION ALL
SELECT '25','21','Voice recognition software', UNION ALL
SELECT '26','2','Desktop publishing software', UNION ALL
SELECT '27','2','Presentation software', UNION ALL
SELECT '28','2','Word processors', UNION ALL
SELECT '31','21','Computer aided design (CAD) software', UNION ALL
SELECT '32','21','Graphics/photo imaging software', UNION ALL
SELECT '38','2','Project management software', UNION ALL
SELECT '42','2','Spreadsheet software', UNION ALL
SELECT '44','21','Music/sound editing software', UNION ALL
SELECT '47','21','Labeling software', UNION ALL
SELECT '51','55','Development software', UNION ALL
SELECT '55','2','Computer utilities', UNION ALL
SELECT '56','55','Backup/recovery software', UNION ALL
SELECT '57','55','Bar coding software', UNION ALL
SELECT '58','55','Security management software', UNION ALL
SELECT '59','21','Foreign language translation software', UNION ALL
SELECT '60','55','Print utilities', UNION ALL
SELECT '62','2','Antivirus & security software', UNION ALL
SELECT '63','55','General utility software', UNION ALL
SELECT '65','55','System management software', UNION ALL
SELECT '66','55','Storage software', UNION ALL
SELECT '67','2','Networking software', UNION ALL
SELECT '73','67','Virtualization/terminal emulation software', UNION ALL
SELECT '80','67','Switch & router software', UNION ALL
SELECT '82','67','Communications server software', UNION ALL
SELECT '87','67','Modem software', UNION ALL
SELECT '88','67','Remote access software', UNION ALL
SELECT '89','21','Video software', UNION ALL
SELECT '94','21','Game software', UNION ALL
SELECT '95','21','Screen savers', UNION ALL
SELECT '98','21','Family titles', UNION ALL
SELECT '99','21','Educational software', UNION ALL
SELECT '106','0','Components', UNION ALL
SELECT '107','0','PDA, GPS & mobile', UNION ALL
SELECT '114','242','Telephone switching equipment', UNION ALL
SELECT '115','242','Switchboard equipment', UNION ALL
SELECT '117','242','Telephones', UNION ALL
SELECT '119','107','Mobile phones', UNION ALL
SELECT '122','242','Answering machines', UNION ALL
SELECT '123','107','Headsets', UNION ALL
SELECT '125','242','Call management system', UNION ALL
SELECT '127','242','Telephone rests', UNION ALL
SELECT '137','242','Antennas', UNION ALL
SELECT '142','242','Teleconferencing equipment', UNION ALL
SELECT '148','242','Line conditioners', UNION ALL
SELECT '150','0','Computers', UNION ALL
SELECT '151','150','Notebooks/laptops', UNION ALL
SELECT '152','150','Docking stations', UNION ALL
SELECT '153','150','PCs', UNION ALL
SELECT '154','107','PDAs', UNION ALL
SELECT '155','150','Mainframe computers', UNION ALL
SELECT '156','150','Servers', UNION ALL
SELECT '157','166','Port replicators', UNION ALL
May 31, 2012 at 8:45 am
Well done posting ddl and sample data. Had a couple typos but not a big deal.
In order to do what you are trying you need to use what is called a recursive common table expressions or rCTE.
Given your sample let's say you wanted to find CategoryID 55 (Computer utilities) and all categories "up the chain". Something like this should work
declare @CatID int = 55
;with cte as
(
select categories_name, categories_id from CatIdPidName where categories_id = @CatID
union all
select c.categories_name, c.categories_id
from CatIdPidName c
join cte on cte.categories_id = c.parent_id
)
select * from cte
I leave it up to you to read up and understand how this works. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 9:06 am
Hey Sean thanks very much for that it does just what I wanted (Its hard sometimes not knowing where to start). Now going to read up some more and work out how to get the data back for all my models.
Thanks again.
John.
May 31, 2012 at 9:12 am
You're welcome. Glad that worked for you. The code I shared will get everything for a given category. Not sure what else you are looking for but let me know if you need some help and I will see what I can do. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 12:23 pm
Hi Sean thanks again for earlier it got me on the right track.
I actually needed to find all the category id's going the other way so I had a look around now I know it was CTE I needed I found a simular script and modified it to work with my db see below.
WITH ParentandChild([CatID], [ParentID]) AS
(
-- self (anchor part)
SELECT
[CatID] = categories_id,
[ParentID] = parent_id
FROM dbo.catnamesandids
WHERE
categories_id = 73
UNION ALL
-- Parents (recursive part)
SELECT
[CatID] = categories_id,
[ParentID] = parent_id
FROM ParentandChild AS CTE, dbo.catnamesandids AS T1
WHERE CTE.[ParentID] = T1.categories_id
)
SELECT *
FROM ParentandChild
this is what i used and it worked great, What i need is to feed the category id (currently 73) to this process from another table and then store the results. Can you point me in the right direction again please.
(I dont mind having a read 🙂 and It helps me learn, Im very new to sql
Thanks
John
May 31, 2012 at 3:00 pm
I don't quite follow what you are trying to do but I suspect that what you have here is pretty close. You can change that select to be a an insert if that helps. If that isn't what you meant you need to explain either more clearly or slightly differently so it makes sense in my shallow and hard head.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 3:27 pm
🙂
The script above works fine and returns the results I need for one category (73) but I would like the results for lots of categories so instead of just inserting the number ie(73) how would i link this to another table eg dbo.partsandcatids and send the whole column for processing from this table to the script.
Does this make sense. Its hard somtimes trying to explain in words what would be easy to show.
Thanks for your help..
John.
May 31, 2012 at 3:42 pm
dallibabs (5/31/2012)
🙂The script above works fine and returns the results I need for one category (73) but I would like the results for lots of categories so instead of just inserting the number ie(73) how would i link this to another table eg dbo.partsandcatids and send the whole column for processing from this table to the script.
Does this make sense. Its hard somtimes trying to explain in words what would be easy to show.
Thanks for your help..
John.
Well like you said it is hard to explain. It is even harder to understand exactly what you are looking for. 😛
Before I could have much chance helping you I would need ddl and sample data for this other table. Then the desired output. Basically the same type of information you posted originally, just need more of it now.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2012 at 3:48 pm
Thanks Sean, Ill be back on my server in the morning so Ill try to post it up then.
Thanks for helping this had been driving me mad for a couple of days now 🙂
May 31, 2012 at 3:53 pm
No problem. I am about to wrap up for the day too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply