CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlselect
Phill Healey
Phill Healey

Reputation: 3180

Combine 2 SQL SELECT statements

** Evidently some people think my question is not worthy of their time. I whole heartedly appologise for this. However, rather than down voting why not use that time to do something positive and at least tell me what info you would require to make this not be a cr@p question in your eyes. **

I have a list of staff in table tblMembers and a list of clients in table tblClients.

One person may have several client.

The staff member associated with a client is identified by staffId against the client record.

Each staff member has a category Id for the type of clients they have catId.

I need to find all of the staff for a given client type and then sort them by the number of clients they have. Staff members without any clients should show a result of 0 rather than not showing.

A simplified table structure would be:

tblMembers:

Id | catId

tblClients:

Id | staffId

Any help would be greatly appreciated.

Thanks!

Upvotes: 1

Views: 1369

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

Its fairly simple to do a join/group and count

SELECT
   s.id,
   s.catid,
   COUNT(c.id)
FROM 
   tblMembers s
   LEFT JOIN tblClients  c
   ON s.id = c.staffid
WHERE
   s.catid = @catID
GROUP BY 
  s.id,
  s.catid
ORDER BY 
    COUNT(c.id) desc

However the one tricky bit is

show a result of 0 rather than not showing.

To do this you need to do a left join to make sure they show even if there are no matching records and you need to make sure to count a field on the table on the Right side of the join. Otherwise you'd get a count of 1

DEMO

Upvotes: 2

Jeroen Vuurens
Jeroen Vuurens

Reputation: 1251

try:

select tblMembers.id, count(tblClient.id)
from tblMembers left join tblCLient on staffId = tblMembers.id
where tblMembers.catId = ??
group by tblMembers.id
order by 2 desc

Upvotes: 1

Gidil
Gidil

Reputation: 4137

Hope I correctly understood your case.
Try something like this:

SELECT T1.ID, 
       Count(*) 
FROM   MEMBERS T1 
       INNER JOIN CLIENTS T2 
               ON T1.ID = T2.STAFFID 
WHERE  T1.CATID = 2 
GROUP  BY T1.ID 
UNION 
SELECT DISTINCT ID, 
                0 
FROM   MEMBERS 
WHERE  CATID != 2

A working sample is available here.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT m.Id 'Member Id', ISNULL(c.StaffCount, 0) 'StuffCount'
FROM tblMembers m
LEFT JOIN
(
    SELECT staffId, COUNT(staffId) 'StaffCount'
    FROM tblClients
    GROUP BY staffId
) c ON m.Id = c.staffId
WHERE m.Cat = 'Some Id'
ORDER BY StuffCount

Upvotes: 2

Related Questions