Loading

sql inner join query with specifc condition column

sql inner join query with specifc condition column

Santosh Sasupalli

8y883

hi All,

please help me,

i have 2 tables like this

parent table:

id name

----------------

1 pa

2 pb

3 pc

4 pd

child table:

-----------------

id pid name status

--------------------------

1 1 ca 1

2 2 cb 2

3 1 cc -1

4 1 cd -1

5 2 ce 1

6 3 cf -1

7 4 cg 1

now i want out put like this:

pid cname

1 cc,cd

3 cf

which mean which child status=-1 that names return , separeted and having count(status=-1)>0

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Kiran Chanduri8y ago
Hey Santhosh ,

Please check the following code , it could be ur required solution
create Table #Parent
(ID int , Name VArchar(100))
insert into #Parent(ID,Name) Values(1,'pa'),
(2,'pb'),
(3,'pc'),
(4,'pd')

create Table #Child
(ID int , Name VArchar(100),pid int,status int)
insert into #Child(ID,pid,Name,status) Values
(1,1,'ca',1),
(2,2,'cb',2),
(3,1,'cc',-1),
(4,1,'cd',-1),
(5,2,'ce',1),
(6,3,'cf',-1),
(7,4,'cg',1)


-- to get Status =-1
select * from #Parent P
inner join #Child C On P.ID=c.pid and c.Status =-1

-- to get Status =-1 with comma seperated C- names
select distinct C.PID,P.Name,
Stuff((Select ','+C1.Name
From #Child C1 Where C.pID=C1.pID AND C1.Status=-1

For XML PATH('')),1,1,'') AS Names
from #Child C


inner join #Parent P On P.ID=c.pid and c.Status =-1
Group BY C.ID,C.PID,c.Status,P.Name
0
Ankit Sharma8y ago
Hi Santosh,

I dont think parent table is required to get this output.

Please try the below sql query and let me know if it works for you
SELECT DISTINCT pid,STUFF((SELECT distinct ',' + name
        FROM child c1
        WHERE [status]=-1
        and c1.pid=c2.pid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') Cname
FROM child c2
WHERE [status]=-1
+1