How to code to find out different names with the same memberid?

Question

Post reply

How to code to find out different names with the same memberid?

adonetok

SSCrazy Eights

Points: 8395
More actions
January 5, 2017 at 8:15 am

#320247

There are memberid and membername in one table member.
One memberid shoud match only one membername.
How to code to find out different names with the same memberid?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

Select Count(Names),MemberId,Names from Table_Sample group by MemberID Having Count(Names)>1

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Use HAVING.

GROUP BY memberid HAVING COUNT( DISTINCT membername) > 1

or

GROUP BY memberid HAVING MAX(membername) <> MIN(membername)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

adonetok (1/5/2017)
There are memberid and membername in one table member.
One memberid shoud match only one membername.
How to code to find out different names with the same memberid?

select memberid, membername

from member

group by memberid

having count(membername) > 1

Untested and syntax unverified (I typed this from memory).

This is a very simply query. You should become friends with Books Online, his resource would have answered your question.

drew.allen SSC Guru Points: 76996 More actions · Answer 4

Lynn Pettis (1/5/2017)
adonetok (1/5/2017)
There are memberid and membername in one table member.
One memberid shoud match only one membername.
How to code to find out different names with the same memberid?
select memberid, membername
from member
group by memberid
having count(membername) > 1
Untested and syntax unverified (I typed this from memory).
This is a very simply query. You should become friends with Books Online, his resource would have answered your question.

Since you're not grouping by membername, it needs to be aggregated to be in the select. You may or may not want to use a distinct count.

select memberid, COUNT(membername) AS cnt, COUNT(DISTINCT membername) AS distinct_cnt

from member

group by memberid

having count(membername) > 1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA