Trying to do a COUNT/AS RANK and it will not work.

[K3nSchr0eder]

Hello everyone! I am new to SQL and have given myself a 3 Day crash course! I apologize for my lack of knowledge. A project was assigned to me for work and so now I need to get it done.

I am running SQL SERVER 2008. I have one Database. It is called "HomeHealth". It has 3 Tables.

1.HomeCareAgencies (Holds the Agency Information such as name, address, phone, as well as an ID that is called Mnemonic in the table.
2.MoCounties (It holds, County Names, Services Offered and also has the Mnemonic field. This table is for 1 State.
3. ILCounties (Same as MoCounties but is for a second state.

Now, I need to be able to run a query that will give me all fields from HomeCareAgencies, MoCounties County Name and Services as well as ILCounties County Name and Services.
Seems like it would be easy and in fact I have accomplished this. However, It creates a result of about 9,000 rows because if one home Care Agency is in multiple counties in both states and offers multiple services it displays it in individual lines. I need it to have one line for the Agency to include all of the MoCounties, IlCounties and all services that it provides.

I tried using a Count(*)AS Rank in my query but it flakes out.

This Query works great:
________________________________________________

SELECT Services, County, 
   COUNT(*) AS Rank
FROM ILCounties AS IlCounties
   INNER JOIN HomeCareAgencies AS HomeCareAgencies
   ON ILCounties.Mnemonic = HomeCareAgencies.Mnemonic
      AND ILCounties.Mnemonic  <= HomeCareAgencies.Mnemonic
GROUP BY Services, 
   County

__________________________________________________

However it doesn't include the Data from the HomeCareAgencies table.

I am sorry for the length of this. Any and ALL suggestions are extremely appreciated!! Thank you sincerely.

[NeoPa]

Although you have laid out your question very well Ken, especially for one who is new to the site, I'm not sure I follow it all too well.

What I do see though, is that you will probably need, as a start, a base query that has [HomeCareAgencies] on the left, with LEFT JOINs to both of the other two tables. Something like :

...
FROM   ([HomeCareAgencies]
       LEFT JOIN
       [ILCounties]
  ON   HomeCareAgencies.Mnemonic=ILCounties.Mnemonic)
       LEFT JOIN
       [MOCounties]
  ON   HomeCareAgencies.Mnemonic=MOCounties.Mnemonic

I would add that having your counties stored in two separate tables is unlikely to be Normalised (See Database Normalisation and Table Structures), and should probably (almost certainly) be avoided.

I'm guessing here, but if you have counties that belong in Missouri an Illinois then they should all be stored in the same table with a simple field that indicates which state they're from.

PS. The AS (or ALIAS) keyword is provided to give a shorthand version of an object. It makes little sense to rename it as itself.

[Rabbit]

I second NeoPa's comment about normalization.

Also, I don't understand why you are asking about the count function when it sounds like you are only using count in attempt to solve a problem and is not the problem itself.

The problem, if I am reading correctly, is not the count function, but that you want to concatenate multiple rows. In SQL Server 2008 and up, you have access to XML functionality that can do this for you.

An example of this is:

select ', ' + someField
from someTable
for XML path('')

You can use that in a subquery to concatenate multiple rows into one.

[K3nSchr0eder]

@NeoPa and @Rabbit- Thank you both for your responses. I cant't combine the Counties tables because they are used in a different program independently. So that is one of the hurdles I have. I guess the main issue I am trying to resolve is, that if one of the Agencies provides 5 different services it list that agency 5 times. I need it to list the agency 1 time with all 5 services listed for that 1 agency. I think that is what @Rabbit is trying to tell me but like I said at the beginning I have only been using SQL for 3 days so not quite sure. Thank you all again for your assistance.

[Rabbit]

Even if you can't normalize your data, you will still use the solution in my post.

[K3nSchr0eder]

@ Rabbit,

I now have this statement:

SELECT     HomeCareAgencies.*,ILCounties.County,ILCounties.Mnemonic,ILCounties.Services,MoCounties.County,
 
                     ((SELECT',' + Services
                      FROM ILCounties AS ILC
                      FOR XML PATH('')))AS ILServicesAndCounties
 
 
FROM         HomeCareAgencies LEFT JOIN dbo.ILCounties ON HomeCareAgencies.Mnemonic = ILCounties.Mnemonic
             LEFT Join MoCounties ON HomeCareAgencies.Mnemonic = MoCounties.Mnemonic

It works as stated, however it is still not quite right. If one agency has Five services and 4 counties where it provides that service it lists that Agency info 4 times. So I guess what you provided is what I was wanting but is there a way to make it not list duplicates?
For example:

Currently reports like this
NAME | County | Services
Agency1|County1|Service,Service,Service1,Service1, Service1

I would like for it to look like this
NAME | County | Services
Agency1|County1|Service,Service1

Does that make sense? Is that even possible?? Again thank you so much for your assistance.

[Rabbit]

To remove duplicates, use the distinct clause in the subquery.

Your subquery requires a reference to the outer query to return only the records for a particular agency, otherwise you're going to get every service for every agency.

Also, you probably don't want to join to the county table because you will get duplicate rows.

[K3nSchr0eder]

@Rabbit YOU ARE A GENIUS!!
So here is the code I have got so far:

SELECT HomeCareAgencies.*,MoCounties.ServicesOffered,MoCounties.Mnemonic,
                ((SELECT DISTINCT County + ',' 
                      FROM MoCounties AS MC
                      WHERE MC.Mnemonic = HomeCareAgencies.Mnemonic
                      FOR XML PATH('')))AS MoCounty
FROM         [HomeCareAgencies],MoCounties

It displays one line with all Agency information and all the counties that it services in one field. Is it possible to have a subquery inside a subquery? I need to do this same thing for the services as well as the counties and services for the other state. I feel like this is getting REAL close to what I need. You are amazing!

[Rabbit]

Yes, you can nest subqueries. I would suggest a union subquery in place of the table MoCounties.

I notice you still have MoCounties in your outer query. I though your goal was to have one row per agency? If that's what you want, you can't have MoCounties in your outer query.

[NeoPa]

K3nSchr0eder:
@Rabbit YOU ARE A GENIUS!!

Not far off as it happens. His latest point is also worth considering carefully ;-)

[K3nSchr0eder]

Thank you both sooo much for your assistance. I have now got exactly what I wanted.
Here is the final query:

SELECT     HomeCareAgencies.*,
           ((SELECT DISTINCT',' + Services 
              FROM ILCounties 
              WHERE HomeCareAgencies.Mnemonic = ILCounties.Mnemonic
              FOR XML PATH('')))AS ILService,
              ((SELECT DISTINCT ',' + County
                FROM ILCounties
                WHERE HomeCareAgencies.Mnemonic = ILCounties.Mnemonic
                FOR XML PATH(''))) AS ILCounties,
                 ((SELECT DISTINCT ',' + Services
                   FROM MoCounties
                   WHERE HomeCareAgencies.Mnemonic = MoCounties.Mnemonic
                   FOR XML PATH(''))) AS MoServices,
                    ((SELECT DISTINCT ',' + County
                      FROM MoCounties
                      WHERE HomeCareAgencies.Mnemonic = MoCounties.Mnemonic
                      FOR XML PATH(''))) AS MoCounties
 
FROM HomeCareAgencies 

Genius is an understatement for you both!

[ck9663]

Be careful, that looks like an RBAR.

From the looks of it, your requirement look like this

Good Luck, nevertheless!!!


~~ CK

[NeoPa]

You may find RBAR: 'Row By Agonizing Row' helpful in order to understand where CK is going with that ;-)