Get all Parent Record ID in one field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get all Parent Record ID in one field

Author

Topic

studyy
Starting Member

16 Posts

Posted - 2011-04-10 : 15:53:45

Hi

I Search forum but not able to find the solution :(

I need query which have fields that have data of its parent.

Mytable1
Pid
Pname
Ptitle
PparentID

Pid Pname Ptitle PparentID
1 name1 title1 0
2 name2 title2 1
3 name3 title3 1
4 name4 title4 3
5 name5 title5 4

So what i want that
when i run slect query for say id=5 then
it should give me fallwoing result

Pid Pname Ptitle PparentID AllParent
5 name5 title5 4 4,3,1

How to achive this.

I also want that i can get this on accdb for offilne work.
so target sql for sql 2008 & accdb if possible

thx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 16:34:15

I don't know how to do it in Access, but in SQL 2008 here is a way to do it.


declare @Pid int;
set @Pid = 5;

with cte as
(
   select 
      m.Pid,
      m.Pname,
      m.Ptitle,
      m.PparentID,
      m.PparentID as NexPID,
      cast(m.PparentID as varchar(max)) ParentString,
      1 as Lvl
   from
      MyTable1 m
   where
      m.Pid = @Pid
   
   union all
   
   select
      c.Pid,
      c.Pname,
      c.Ptitle,
      c.PparentID,
      m.PparentID,
      c.ParentString + ',' + cast(m.PparentID as varchar(max)),
      Lvl+1
   from
      MyTable1 m
      inner join cte c on c.NexPID = m.Pid
   where
      m.PparentID <> 0
)
select top 1 Pid, Pname, Ptitle, PparentId, ParentString 
from cte 
order by Lvl desc;

studyy
Starting Member

16 Posts

Posted - 2011-04-11 : 05:27:46

Thanks Sunita for quick Reply & solution,

I m thinking to make use of subquery to achive this in accdb is it possible ? parent go to upto 5 levels

SELECT
testparent.pid,
testparent.pname,
testparent.parentID,
(Select top 1 t2.parentID from testparent t2 where t2.parentID=testparent.pid ) as XTParent,
(Select top 1 t3.parentID from testparent t3 where t3.parentID=XTParent ) as XTParent2
,(XTParent + ',' + XTParent2) as XTParentALL
FROM
testparent

but it not working as i need.

can u help me on this too ?

Thanks

studyy
Starting Member

16 Posts

Posted - 2011-04-11 : 06:46:17

Hi

Got a code working on mysql but when tried in access its not working & keep asking for value for custom fields "XTParent" & XTParent2

SELECT
testparent.pid,
testparent.pname,
testparent.parentID,
(Select t2.parentID from testparent t2 where t2.pID=testparent.parentID limit 1 ) AS XTParent,
(Select t3.parentID from testparent t3 where t3.pID=XTParent limit 1 ) AS XTParent2,
(Select t4.parentID from testparent t4 where t4.pID=XTParent2 limit 1 ) AS XTParent3 FROM
testparent

Acces Code

SELECT
testparent.pid,
testparent.pname,
testparent.parentID,
(Select top 1 t2.parentID from testparent t2 where t2.pID=testparent.parentID) AS XTParent,
(Select top 1 t3.parentID from testparent t3 where t3.pID=[XTParent]) AS XTParent2,
(Select top 1 t4.parentID from testparent t4 where t4.pID=[XTParent2]) AS XTParent3
FROM
testparent

its look like ms access is not able to use custom fields in same query and user get popup for value for them "XTParent" , "XTParent2")

but i saw on net that we can use it , may be i m doing some wrong so you guys can spot the problem.

thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 19:19:10

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Please learn why fields and columns are different. Please learn that "patent" and "child" are terms from network databases. I think that you are trying to use an adjacency list model for a hierarchy.

You also gave an example that violated 1NF.

Google the "Nested Sets model" and use it instead.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources