Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
SQL Help on obscure query please |
Sat, Jun 9 2012 11:25 AM | Permanent Link |
Adam Brett Orixa Systems | I have a fairly simple problem, which I can't seem to figure out in SQL.
I have a MembershipDetails table with about 400,000 entries. Every row is for 1 person, as a member of 1 society within a larger group. Once a member joins society X they are supposed to stay a member of that society. However, sometimes errors are made and in 1 years data and a member has been given a membership record for the wrong society. I need to find these "bad" records. There are probably hundreds, perhaps thousands. -- SQL in this form: SELECT PeopleID, SocietiesID FROM MembershipDetails GROUP BY MembershipDetails Returns 1 row for _most_ members & 2 rows for the "bad" entries. But how can I write a script to return only those members who appear more than once? Note that members may have been members for any number of years ... i.e. different members have different numbers of membership rows, so I can't use a count of total entries per user to help me out. ... thanks in advance for any help. Adam |
Sat, Jun 9 2012 3:53 PM | Permanent Link |
Raul Team Elevate | Generally you would use HAVING clause for this as it complements the
GROUP BY by providing filtering. I'm not near my edb system so sql below is likely rubbish but general usage would be something like this: SELECT PeopleID ,SocietiesID FROM MembershipDetails GROUP BY MembershipDetails HAVING COUNT(*) > 1 Raul On 6/9/2012 11:25 AM, Adam Brett wrote: > I have a fairly simple problem, which I can't seem to figure out in SQL. > > I have a MembershipDetails table with about 400,000 entries. > > Every row is for 1 person, as a member of 1 society within a larger group. > > Once a member joins society X they are supposed to stay a member of that society. > > However, sometimes errors are made and in 1 years data and a member has been given a membership record for the wrong society. > > I need to find these "bad" records. There are probably hundreds, perhaps thousands. > > -- > > SQL in this form: > > SELECT > PeopleID, > SocietiesID > FROM MembershipDetails > GROUP BY MembershipDetails > > Returns 1 row for _most_ members& 2 rows for the "bad" entries. > > But how can I write a script to return only those members who appear more than once? > > Note that members may have been members for any number of years ... i.e. different members have different numbers of membership rows, so I can't use a count of total entries per user to help me out. > > .. thanks in advance for any help. > > Adam > |
Sun, Jun 10 2012 3:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Up to <<Returns 1 row for _most_ members & 2 rows for the "bad" entries.>> I would go along with Raul's suggestion, however, the next paragraph makes me wonder. <<. different members have different numbers of membership rows, so I can't use a count of total entries per user to help me out>> GROUP BY / HAVING is filtering on exactly this. Either more explanation or less caffeine is called for Roy Lambert Roy Lambert [Team Elevate] |
Mon, Jun 11 2012 9:11 AM | Permanent Link |
Adam Brett Orixa Systems | Thanks Roy
I thought exactly what you thought, but it doesn't work. I think this may actually be impossible to query ... Data User Society 1 1 1 1 1 1 2 1 2 1 3 1 3 1 3 2 I want to locate user 3, i.e. the user who has 2 different societies listed. GROUP BY just totals the rows for each User / Society pairing. If I GROUP BY User, Society then 3 OCCURS twice, all the others only OCCUR once. But I don't know how to capture this duplicate occurence in a WHERE. GROUP BY User Gives a COUNT(*) of 2 for User 3. GROUP BY User, Society Gives a COUNT(*) of 1, 1 for User 3. |
Mon, Jun 11 2012 9:33 AM | Permanent Link |
Raul Team Elevate | How about just something like this :
SELECT distinct S.user FROM UserSociety S WHERE S.User IN ( SELECT U.USER FROM UserSociety U WHERE U.Society <> S.Society) Raul On 6/11/2012 9:11 AM, Adam Brett wrote: > Thanks Roy > > I thought exactly what you thought, but it doesn't work. I think this may actually be impossible to query ... > > Data > > User Society > 1 1 > 1 1 > 1 1 > 2 1 > 2 1 > 3 1 > 3 1 > 3 2 > > I want to locate user 3, i.e. the user who has 2 different societies listed. > > GROUP BY just totals the rows for each User / Society pairing. > > If I GROUP BY User, Society then 3 OCCURS twice, all the others only OCCUR once. > > But I don't know how to capture this duplicate occurence in a WHERE. > > GROUP BY User Gives a COUNT(*) of 2 for User 3. > GROUP BY User, Society Gives a COUNT(*) of 1, 1 for User 3. > |
Mon, Jun 11 2012 10:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Raul
Works nicely Roy Lambert [Team Elevate] |
Mon, Jun 11 2012 2:08 PM | Permanent Link |
Adam Brett Orixa Systems | WOW.
Thanks Raul. I normally only use that type of join in an UPDATE statement. I've never seen it used like that. Adam |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |