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

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
>

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]

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.

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.
>

Raul


Works nicely

Roy Lambert [Team Elevate]

WOW.

Thanks Raul. I normally only use that type of join in an UPDATE statement. I've never seen it used like that.

Adam