0

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?

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by nschessnerd
2

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)
Votes + Comments
Great! (New nice helper on SQL Server Forum)
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.