Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ruski86
Starting Member
2 Posts |
 Posted - 2010-03-18 : 03:55:10
|
| I'm new here, so I'm not sure if I'm posting in the right place. If not, if someone could direct me to where I should post, I'd appreciate.Anyway, my problem is that I have a query where I'm trying to get the number of people who have zip codes associated with different "federations." Each person has three different zip codes: primary, secondary, and university. My goal is, given a list of federations, to find the number of people who have primary zip codes associated with that federation, secondary zip codes associated with it, university zip codes associated with it, and all possible combinations of the three. The RegMember table simply contains basic information for people (including their primary zip, secondary zip, and university). RegUniversity links to their university's zip. The FedZipDeDupe table has associations between zip codes and federations. The main problem that's ruining all my queries is that the FedZipDeDupe table sometimes has TWO OR MORE different federations associated with a given zip code. Without this annoyance, I could have simply used my query below as a basis, and then run queries using it to find the numbers I need, but as is, I'm completely stuck. I'd really love to grab the numbers I need with one query and not have to hit it 7 times, but I'm very stumped right now. Any ideas? The current query I have that just brings up the names of the federations a given person is registered with (and that brings up too many entries due to the joins occurring in the query when a zip code is associated with more than one federation) is:SELECT Regmember.IdMember, IdRound, CASE WHEN FedZipDedupe.fedCity IN ('Toronto','New York','Memphis') THEN FedZipDedupe.fedCity ELSE NULL END AS PrimCheck, CASE WHEN SecZipDedupe.fedCity IN ('Toronto','New York','Memphis') THEN SecZipDedupe.fedCity ELSE NULL END AS SecCheck, CASE WHEN UnivZipDedupe.fedCity IN ('Toronto','New York','Memphis') THEN UnivZipDedupe.fedCity ELSE NULL END AS UnivCheck FROM RegMember LEFT OUTER JOIN RegUniversity ON RegMember.University = RegUniversity.UnivCode LEFT OUTER JOIN fedzipdedupe ON (LEFT(RegMember.primzip, 5) = fedzipdedupe.FedZip OR LEFT(RegMember.primzip, 2) = fedzipdedupe.FedZip) LEFT OUTER JOIN FedZipDedupe SecZipDedupe ON (LEFT(RegMember.seczip, 5) = Seczipdedupe.FedZip OR LEFT(RegMember.seczip, 2) = Seczipdedupe.FedZip) LEFT OUTER JOIN FedZipDedupe UnivZipDedupe ON (LEFT(RegUniversity.ZipCode, 5) = UnivZipDedupe.FedZip OR LEFT(RegUniversity.ZipCode, 2) = Univzipdedupe.FedZip) WHERE (fedzipdedupe.FedCity IN ('Toronto','New York','Memphis') OR Seczipdedupe.FedCity IN ('Toronto','New York','Memphis') OR Univzipdedupe.FedCity IN ('Toronto','New York','Memphis')) ORDER BY regmember.idmemberAny help you guys can give would be really appreciated as I'm completely stuck right now. As an aside, I currently have a way of getting the information I need using 7 different queries where I don't run into the problem I mentioned above, but it requires four extra indexes to the RegMember table to run even half decently, and even then, it can really only do 6 or 7 big federations in barely under a minute, which is way too long. So, the goal here is to get something that runs pretty quickly and scales up well. That's why I was hoping for a one query approach. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 12:25:27
|
| ok so for ones with multiple matches, which one you need to return in output? first or last? also what should be basis for ordering them?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ruski86
Starting Member
2 Posts |
Posted - 2010-03-18 : 12:59:42
|
| Hey Visakh,Sorry I wasn't clearer. The idea is that in the end I just want the counts (i.e. the number of people who have a primary zip code associated with a certain federation, the number of people who have a secondary zip code associated with that federation, the number who have a university associated with it, the number who have both a primary and a secondary associated with it, etc...). The problem is that because of the joins in the query, if someone has a zip code that's associated with two different federations, it returns multiple matches for that person, which screws up the counts. An example: say a person has the same zip code for his primary, secondary, and university. Let's say that zip code is linked to both New York and Long Island. Finally, let's say I'm doing a query for people who are linked with the New York federation or the Long Island Federation. In that case, the ideal would be that the system would return two rows for that person. One row would have New York for primary, secondary, and university, and the other would have Long Island for each. The problem is that because of the joins in the query, it actually returns 8 rows instead with every possible combination of New York and Long Island. The first couple would look like:New York, New York, New YorkNew York, New York, Long IslandNew York, Long Island, Long Islandetc...This completely messes up my counts because when I'm looking for, say, the number of people with a primary zip code in the New York federation, it adds 4 rows for that one person when it should only add one.Do you see what I mean? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-18 : 13:17:30
|
| ok. so you want them to be considered once and return count of 1 for each person?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Removing Rows from Complicated SQL query