multiple join help

[nschessnerd]

Hey, so i have 4 tables i need to join together. basically this is how it looks

table j:
| id | uid | cid | text |
| 1 | 3 | 1 | blah|
| 2 | 1 | 3 | blah|

table u
| id | name |
| 1 | prsn1|
| 2 | prsn2 |
| 3 | prsn3 |

table c:
| id | name |
| 1 | asd |
| 2 | fgh |

table d
| jid | text |
| 1 | blah |
| 1 | blabla|

so i need a query that returns
id | uid | uname | cid | cname | count(d)
| 1 | 3 | prsn3 | 1 | asd | 2
| 2 | 1 | prsn1 | 3 | fgh |0

how do i do this? or is there an easier way?

[sknake]

This should work:

Select u.id, j.uid, u.name, j.cid, c.name, (Select Count(*) From d where d.id=j.id) as cnt
From u inner join j on (u.id=j.id)
       inner join c on (c.id=j.id)

[nschessnerd]

great! that worked
thanks