sql inner join query with specifc condition column

Question

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