I can't think of an obvious one, but you can find out which email
addresses are duplicated like this:
SELECT mem.Email, COUNT(grp.GroupID)
FROM tblcurrentCouncilMembers mem
JOIN tblCouncilEmailGroup grp ON (mem.Member_ID = grp.MemberID)
WHERE grp.GroupID IN (17, 19, 21)
GROUP BY mem.Email
HAVING COUNT(grp.GroupID) > 1
(or possibly HAVING is above GROUP BY - I can never remember the order of them)
Unfortunately that wont actually tell you which ids the emails belong
to - you'd need to do a SELECT groupID ... WHERE Email IN ({list of
duplicated emails}) for that.
On 4/24/07, Bruce Sorge <[EMAIL PROTECTED]> wrote:
> I have a very simple query:
>
> SELECT tblcurrentCouncilMembers.Email, tblCouncilEmailGroup.GroupID
> FROM tblcurrentCouncilMembers INNER JOIN tblCouncilEmailGroup ON
> tblcurrentCouncilMembers.Member_ID = tblCouncilEmailGroup.MemberID
> WHERE tblCouncilEmailGroup.GroupID IN (17, 19, 21)
>
> It works fine except that, as expected, if there is an email address in lets
> say group 17 and 19, it returns the same email address twice. So, can I
> filter the results in SQL so that if there is the same email address in more
> than one groupID, it only outputs that one? If not that is fine, I can do it
> with CF on the output.
>
> Thanks,
>
> --
> Bruce Sorge
>
> "I'm a mawg: half man, half dog. I'm my own best friend!"
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2817
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
