0

Say I have a table with 3 columns A, B, C. I want a result set which denotes those entries which have multiple/duplicate entries for either A OR B but unique entries for C.

I know the GROUP BY clause would come handy in this case, but it seems more involved than that.

For example: Say, a table having customer info with the following columns: 1)ID 2)Phone 3)Email 4)Account

Obtain those IDs which have multiple occurrences of either same Phone OR Email but belonging to different accounts. For instance, for the following entries in the original table:

1) ID: 12 | Phone: 111-111-1111 | Email: [email protected] | Account: 2

2) ID: 14 | Phone: 111-111-1111 | Email: [email protected] | Account: 5

3) ID: 15 | Phone: 123-234-7890 | Email: [email protected] | Account: 12

4) ID: 21 | Phone: 900-893-4563 | Email: [email protected] | Account: 23

5) ID: 17 | Phone: 222-333-1111 | Email: [email protected] | Account: 3

6) ID: 19 | Phone: 222-333-1111 | Email: [email protected] | Account: 3

The Resulting row(s) would be as follows:

1)IDs: 12, 14 | Accounts: 2, 5

2)IDs: 15, 21 | Accounts: 12, 23

Entries #5 and #6 were NOT included because account values not different, albeit phone and email are same.

Thanks in advance!

Improve this question
4
  • In your first example, you say that records with IDs 12 and 14 should be depicted as one row in the result set. How do you decide which of the two is shown? Higher/lower ID? Higher/lower account number? Commented Mar 28, 2018 at 0:25
  • @nb1987 - I should've thought of adding that detail in the question earlier, sorry. Single row with IDs separated by commas in the same column would be appropriate in this case. Commented Mar 28, 2018 at 0:54
  • Thanks for that additional detail. One more question/clarification--is the inclusion criteria that the account differs but the Phone OR Email is the same, or is the inclusion criteria that the account differs but the Phone AND Email are the same? For example, I notice the phone number is the same but the email different in your example. Commented Mar 28, 2018 at 1:03
  • It's either Phone or Email that should be the same or both, so in this case OR would be appropriate. Commented Mar 28, 2018 at 1:09

3 Answers 3

Reset to default
1

You can use group by and group concat to collect all id and accountno value. To remove duplicates by email or phone number, use having count distinct. Combine all result via Union all. Union is better than union since we are sure that duplicates are removed.

Demo: http://sqlfiddle.com/#!9/e0af39/2

Select group_concat(id order by id) as id,
group_concat(accountNo order by accountNo) as
accountno
From Tbl
Group by Email
Having count(distinct accountno) > 1
UNION ALL
Select group_concat(id order by id) as id,
group_concat(accountNo order by accountNo) as
 accountno
 From Tbl
 Group by Phone
 Having count(distinct accountno) > 1


Result:
 id accountno
13,17   4,8
12,14   2,5
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the reply, but it didn't work in my case. I've added more details to my question for more clarity.
Thanks for updating more sample data. I updated my answer.
Thanks for the answer, I finally got yours working for my use case!
1

I've created a SQL Fiddle that should work for your case:

http://sqlfiddle.com/#!9/6b77b1/2

The query is below:

SELECT group_concat(t.id order by t.id) as ids
, group_concat(t.accountNo order by t.accountNo) as accounts
FROM tbl t 
JOIN tbl t2 
    ON t2.accountno != t.accountno 
    AND t2.Phone = t.Phone 

UNION 

SELECT group_concat(t.id order by t.id) as ids
, group_concat(t.accountNo order by t.accountNo) as accounts
FROM tbl t 
JOIN tbl t2 
    ON t2.accountno != t.accountno 
    AND t2.Email = t.Email

Basically, the table does a JOIN against itself on your desired criteria (unequal account and equal phone OR unequal account and equal email), then uses an aggregation function (GROUP_CONCACT() for MySQL) to put the matched records on the same row.

Also, credit to the user âńōŋŷXmoůŜ as I piggy-backed off of the schema for his SQL Fiddle.

Improve this answer

3 Comments

You are welcome. I also upvoted your answer. Also take note that the SO is in mySql so better use it if possible. Group_concat works well in mysql when I try it.
Thanks for the upvote; I have given one in turn since your answer works fine as well. I see GROUP_CONCAT does indeed work; when I initially tried it I must have had some other syntax error, but strangely SQL Fiddle still reported GROUP_CONCAT as being non-existent. I have updated my answer accordingly.
it is a bug in sqlfiddle during editing. Most of the time I re type the same code or remove/add any space then run it again.
0

Try this:

-- records with identical phone but different account
select 
group_concat( id order by id separator ',') id,
group_concat( account order by id separator ',') account
from
test 
where
phone in 
(select phone 
from test
group by phone  having count(distinct account) > 1)  
group by phone
union
-- records with identical email but different account
select 
group_concat( id order by id separator ',') id,
group_concat( account order by id separator ',') account
from
test 
where
email in 
(select email 
from test
group by email  having count(distinct account) > 1)  
group by email
Improve this answer

1 Comment

You can further simplify your answer by putting "having count" outside your query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.