Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Concat based on parent id

Posted on 2014-02-03
9
Medium Priority
?
331 Views
Last Modified: 2014-02-06
Say I have this
id	name	parent
1	cat 1	0
2	cat 2	1
3	cat 3	2
4	cat 4	3
5	cat 5	0
6	cat 6	5
7	cat 7	6

Open in new window


I want to select all rows (or rather store these ID's in each row of the table), but for each row concat the ID's of the parent hierarchy.
So say for cat 3 I would end up with
cat 3     1,2,3

...where 1, 2 and 3 are the ID's of the row itself, the ID of its parent and the ID of the parent. There is never more than 3 levels and the order doesn't matter.
0
Comment
Question by:Gary
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39831210
I would suggest that you add a fourth field to store that in so you don't mess up your ability to trace thru your hierarchy.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 39831222
Gary: I am not sure this helps, but I've seen a similar/different question recently.  Please have a look:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28336520.html
0
 
LVL 58

Author Comment

by:Gary
ID: 39831247
Maybe but that is array based, I'm looking at an sql statement I can run one time on the database.
If nobody else comes along I'll look through it again, bit too much to digest right now.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Expert Comment

by:SriVaddadi
ID: 39831468
You could use Common table Expressions (CTE). Here is a link to the example

http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
0
 
LVL 58

Author Comment

by:Gary
ID: 39836851
What has MSSQL got to do with it?
0
 
LVL 16

Expert Comment

by:SriVaddadi
ID: 39839005
Is nt it in SQL you are trying to achieve this?
0
 
LVL 58

Author Comment

by:Gary
ID: 39839212
Yes, but what you can do in MSSQL is not the same as what you an do in MySQL
0
 
LVL 58

Author Closing Comment

by:Gary
ID: 39839220
Ended up just running a couple of queries to build it as it is only a one off exercise, but the linked to Q has all the logic for doing it.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39839736
Thanks, Gary!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

783 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