querying and joining multiple tables. Only selecting a single records out of many that may exist

Question

I'm having some difficulties with this query, I'm basically not sure if something like this is possible in one shot.

tbl1

 RefNum         ClientID       SWID
 1              17             79
 3              17             90
 18             23             88
 34             18             88
 56             34             90

tbl2

 SWID            SCity
 79              Syracuse
 90              Elmond
 88              Albureque

In tbl1 I can have multiple ClientID records that will have a distinctive RefNum - since that is the primary key; Auto Increment.

I'm only looking to be able to select TOP 1 for each clientID (only one record per Client). Also, I'm hoping to Join tbl2 using SWID in order to get the SCity.

What I hope it looks like in the end is this (wishful thinking) I dont even know if this is possible with one query

Count      City
1          Syracuse
2          Elmond
1          Albureque

The reason why I only have Count = 1 for Albureque is because, for Albureque (SWID = 88), the same client appears 2 (clientID = 17). In this case I'd only count it once.

I have about 480000 records, and I basically need a summary report that will display the count of unduplicated ClientID's by SCity. Any help will be appreciated.

EDIT:

 RefNum         ClientID       SWID
 1              17             79
 2              17             79
 18             23             79
 3              17             90
 87             17             88

Desired end result

 Count              City
 2                  Syracuse (79)
 1                  Elmond  (90)
 1                  Albureque (88)

So basically Client 17 has two instances of same SWID (79) so we only count it as one. But Client 17 also has another record for another SWID 90 and 88, in this case, we still want to count the records as 1 for 90 and 88.

Syracuse has 2 because Client 17 has Syracuse (79) and Client 23 has Syracuse as well - since client 17 has 2 records, we rolled them up into 1 + 1 (Client 23) = 2.

The even tricker part is that SWID might be different sometimes, but the actual City is the SAME. So as example ClientID = 9 can have SWID = 109 and SWID = 198, but in tbl2 - the SCITY is SAN FRANCISCO in both cases. The reason for that is tbl2 is not a reference table really, SWID is actually associated with a Community person, who might be located in the same city but simply different office. So SWID = 109 is John Doe at San Francisco, and SWID =198 is Jane Smith at San Francisco also.

Answer 1

I think you want join and count(distinct):

select t2.scity, count(distinct t1.clientId)
from tbl1 t1 join
     tbl2 t2
     on t1.swid = t2.swid
group by t2.scity;