Solved

Concat based on parent id

Posted on 2014-02-03
9
314 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 83

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 108

Accepted Solution

by:
Ray Paseur earned 500 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
 
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
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.

 
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 108

Expert Comment

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
phpmyadmin 3 33
Optimizing a query 3 34
html input type 3 22
I have many databases and one main one, how can I take there column names from the main one? 4 10
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now