SQL select -one to many joins want to have the manys

Question

I have two tables, TBL_PARENT (parentID, ParentName) and TBL_CHILDREN (ParentID,Child_Name) A Parent can have 0 to many children

What I want is a query to give me a list of parent and their children in single row per parent.

For example

Parent1 John,Mary

Parent2 jane,steve,jana

And the number of rows to be the total number of parents

I tried with left join and it is giving duplicates. Like if I do SELECT P.PARENTID, C.NAME FROM TBL_PARENT P LEFT OUTER JOIN TBL_CHILDREN C — integratedsolns, Aug 15 '13 at 14:24
Asked and answered many times on this forum. Do a search for "sql stuff xml" http://stackoverflow.com/search?q=sql+stuff+xml — Declan_K, Aug 15 '13 at 14:24
Here is the answer you need. It was not the accepted answer to the question, but it is the right answer. https://stackoverflow.com/a/177153/2324286 — Declan_K, Aug 15 '13 at 14:32
Then just use the `SELECT` portion of the attached as a template. It does not need to be wrapped in procedural code to utilize the `STUFF...XML` construct. — Declan_K, Aug 15 '13 at 18:50

score 0 · Answer 1 · edited Aug 15 '13 at 16:47

SELECT COUNT(P.parentID),
P.ParentName, 
C.Child_Name 
FROM TBL_PARENT as P
INNER JOIN TBL_CHILDREN as C  
WHERE P.parentID == c.ParentID
GROUP BY P.ParentName;

The line P.parentID == c.ParentID is doing the Join, and the line count(P.parentID) is doing the count of all the parents and the line GROUP BY P.ParentName is grouping all the rows by the name of the parent so you can display all the children of every single parent.

score 0 · Answer 2 · answered Aug 15 '13 at 14:53

try this query : I have created 3 table 2 of them are already created on your database #parant, #ch and the third one is a temp table to put the result in.

create table #parant (id int , name varchar(10))
create table #ch (id int , name varchar(10), pid int)

insert into #parant select 1,'PA'
insert into #parant select 2,'PB'
insert into #parant select 3,'PC'

insert into #ch select 1,'Ca',1
insert into #ch select 1,'Cb',1
insert into #ch select 1,'Cc',1
insert into #ch select 1,'Cd',3
insert into #ch select 1,'Cf',3
insert into #ch select 1,'Ch',1




create table #testTable (id int, name varchar(10),chid int, chname varchar(10), cpid int)

insert into #testTable 
select x.id , x.name ,isnull( y.id ,0), isnull(y.name,'') ,isnull(y.pid ,0)
from #parant as x
left outer join #ch as y
on x .id = y .pid 

SELECT t.ID, t.name , STUFF(
(SELECT ',' + s.chname
FROM #TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM #TestTable AS t
GROUP BY t.ID, t.name
GO


drop table #testTable 
drop table #ch 
drop table #parant

for the above data i got the following result

1 PA Ca,Cb,Cc,Ch 2 PB
3 PC Cd,Cf

wala rawashdeh, thanks for the answer and it works perfect. Next question is if I have a third table, FRIEND and want to display friends of the parent ( a parent can have 0 to many friends) in the same result set, how do I modify the syntax ? Thanks — integratedsolns, Aug 17 '13 at 13:21

SQL select -one to many joins want to have the manys

2 Answers2