another complex query

Hi, here am i back ..
not sure it's possible to solve it in one query ...
there are four tables:
1) headquarters
--
hqID (primary key)
hqname
2) department
--
depID (primary key)
depname
hqID
3) reports
--
repID (p. key)
depID
userID
4) users
--
userID (p.key)
username
I want to get in one query those four fields:
hqID, hqname, depID, depname + varchar(number of distinct users that has
made a report for
each department) . This last field is a concatenation.
The complexity resides in the fact there is need for a COUNT among other
fields ...
E.g.: table reports may look like this:
repID depID userID
1 1 1
2 1 1
3 1 3
4 3 6
5 4 8
This gives:
for dep 1: 2 distinct users
for dep 2: 0
for dep 3: 1 distinct user
for dep 4: 1 distinct user
Thanks for help
ChrisTry:
select
d.depID
, count (distinct r.userID)
from
departments d
left join
reports r on r.depID = d.depID
group by
d.depID
order by
d.depID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Chris" <ch@.spam.it> wrote in message
news:%23HOudwFBIHA.3900@.TK2MSFTNGP02.phx.gbl...
Hi, here am i back ..
not sure it's possible to solve it in one query ...
there are four tables:
1) headquarters
--
hqID (primary key)
hqname
2) department
--
depID (primary key)
depname
hqID
3) reports
--
repID (p. key)
depID
userID
4) users
--
userID (p.key)
username
I want to get in one query those four fields:
hqID, hqname, depID, depname + varchar(number of distinct users that has
made a report for
each department) . This last field is a concatenation.
The complexity resides in the fact there is need for a COUNT among other
fields ...
E.g.: table reports may look like this:
repID depID userID
1 1 1
2 1 1
3 1 3
4 3 6
5 4 8
This gives:
for dep 1: 2 distinct users
for dep 2: 0
for dep 3: 1 distinct user
for dep 4: 1 distinct user
Thanks for help
Chris|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:estlIZHBIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> d.depID
> , count (distinct r.userID)
> from
> departments d
> left join
> reports r on r.depID = d.depID
> group by
> d.depID
> order by
> d.depID
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Chris" <ch@.spam.it> wrote in message
> news:%23HOudwFBIHA.3900@.TK2MSFTNGP02.phx.gbl...
> Hi, here am i back ..
> not sure it's possible to solve it in one query ...
> there are four tables:
> 1) headquarters
> --
> hqID (primary key)
> hqname
> 2) department
> --
> depID (primary key)
> depname
> hqID
> 3) reports
> --
> repID (p. key)
> depID
> userID
> 4) users
> --
> userID (p.key)
> username
> I want to get in one query those four fields:
> hqID, hqname, depID, depname + varchar(number of distinct users that has
> made a report for
> each department) . This last field is a concatenation.
> The complexity resides in the fact there is need for a COUNT among other
> fields ...
>
> E.g.: table reports may look like this:
> repID depID userID
> 1 1 1
> 2 1 1
> 3 1 3
> 4 3 6
> 5 4 8
> This gives:
> for dep 1: 2 distinct users
> for dep 2: 0
> for dep 3: 1 distinct user
> for dep 4: 1 distinct user
> Thanks for help
> Chris
>