CODEWITHSUNDEEP
sqloracle-database
Mephistofee
Mephistofee

Reputation: 73

Listing Pairs on same table with different criteria on Oracle Sql

I need to list pairs of customer names as follows;

> CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME

such that each pair of customers has the same zip code (which is also in the customers table).

On the task there is a hint which says

HINT: Your output should have four columns: CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME

This is what I have written so far:

SELECT DISTINCT CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME, CUSTOMER_FIRST_NAME, CUSTOMER_LAST_NAME
FROM CUSTOMERS
WHERE CUSTOMER_ZIP = CUSTOMER_ZIP

But I am not sure how to continue since I've only started learning SQL yesterday. I have also tried to Join the same table which resulted in ambiguity errors.

Update#1: I've written this code using aliases as suggested by @kpater87

 SELECT DISTINCT  C1.CUSTOMER_FIRST_NAME, C1.CUSTOMER_LAST_NAME , C2.CUSTOMER_FIRST_NAME, C2.CUSTOMER_LAST_NAME 
FROM CUSTOMERS C1
INNER JOIN CUSTOMERS C2
ON C1.CUSTOMER_ZIP = C2.CUSTOMER_ZIP

But even though I have a distinct statement it will show duplicate data. Is this to be expected or am I missing something?

Upvotes: 2

Views: 1726

Answers (1)

kpater87
kpater87

Reputation: 1270

Your updated query looks fine. The only problem in your query is it will be joining also the same records. If you have a primary key in the table you can improve your query by adding WHERE condition:

SELECT DISTINCT C1.CUSTOMER_FIRST_NAME,
  C1.CUSTOMER_LAST_NAME ,
  C2.CUSTOMER_FIRST_NAME,
  C2.CUSTOMER_LAST_NAME
FROM CUSTOMERS C1
INNER JOIN CUSTOMERS C2
ON C1.CUSTOMER_ZIP = C2.CUSTOMER_ZIP
WHERE C1.PK <> C2.PK;

PK - is a column being a primary key in the table.

If you don't have primary key you can try this one:

SELECT C1.CUSTOMER_FIRST_NAME,
  C1.CUSTOMER_LAST_NAME ,
  C2.CUSTOMER_FIRST_NAME,
  C2.CUSTOMER_LAST_NAME
FROM CUSTOMERS C1
INNER JOIN CUSTOMERS C2
ON C1.CUSTOMER_ZIP = C2.CUSTOMER_ZIP
WHERE C1.CUSTOMER_FIRST_NAME <> C2.CUSTOMER_FIRST_NAME
  AND C2.CUSTOMER_LAST_NAME <> C2.CUSTOMER_LAST_NAME

But there still be a problem that in your output you will get e.g.

Mary Smith James Bond
James Bond Mary Smith

To remove permutations:

SELECT C1.CUSTOMER_FIRST_NAME,
  C1.CUSTOMER_LAST_NAME ,
  C2.CUSTOMER_FIRST_NAME,
  C2.CUSTOMER_LAST_NAME,
  C1.CUSTOMER_ZIP
FROM T_CUSTOMERS C1
LEFT JOIN T_CUSTOMERS C2
ON (C1.CUSTOMER_ZIP = C2.CUSTOMER_ZIP
    AND
    C1.CUSTOMER_NUMBER > C2.CUSTOMER_NUMBER );

See also: SQL: self join using each rows only once

Upvotes: 1

Related Questions