0

I have a table cust_info with columns cust_id and cust_email. Here the same customer can have multiple email IDs.

Now I am trying for a query where each cust_id must be mapped to two different combinations of email. The order does not matter.

My Input table: 

cust_id   cust_email
1001      sample1001_1@email.com
1001      sample1001_2@email.com
1001      sample1001_3@email.com
1001      sample1001_4@email.com
1002      sample1002_1@email.com
1002      sample1002_2@email.com
1002      sample1002_3@email.com

Expected Output:

cust_id  cust_email_1            cust_email_2
1001     sample1001_1@email.com  sample1001_2@email.com
1001     sample1001_1@email.com  sample1001_3@email.com
1001     sample1001_1@email.com  sample1001_4@email.com
1001     sample1001_2@email.com  sample1001_3@email.com
1001     sample1001_2@email.com  sample1001_4@email.com
1001     sample1001_3@email.com  sample1001_4@email.com
1002     sample1002_2@email.com  sample1002_1@email.com
1002     sample1002_2@email.com  sample1002_3@email.com
1002     sample1002_3@email.com  sample1002_1@email.com

I tried using inner join:

SELECT c1.cust_id, c1.cust_email, c2.cust_email
FROM cust_info c1
INNER JOIN cust_info c2
    ON  c1.cust_id = c2.cust_id AND c1.cust_email != c2.cust_email;

But for above-tried query, I am getting permutations instead of combinations that I don't need. For example:

1001    sample1001_1@email.com  sample1001_2@email.com
1001    sample1001_2@email.com  sample1001_1@email.com

I already explored a similar question here but since that query uses a date column, a comparison operator was used for filtering permutation. But in my case, the column is a string.

jarlh
  • 42,561
  • 8
  • 45
  • 63
satznova
  • 520
  • 7
  • 15

1 Answers1

2

Change your join condition to use an < inequality:

SELECT
    c1.cust_id,
    c1.cust_email,
    c2.cust_email
FROM cust_info c1
INNER JOIN cust_info c2
    ON c1.cust_id = c2.cust_id AND
       c1.cust_email < c2.cust_email;

This should prevent the sort of duplicate pairs you are currently seeing.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks Tim, I didn't know that comparison operators can be used with string columns in SQL. [link](https://stackoverflow.com/questions/26080187/sql-string-comparison-greater-than-and-less-than-operators/26081086) – satznova Dec 16 '19 at 09:43