2

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?

Mephistofee
  • 73
  • 1
  • 10
  • 1
    Hint: `JOIN`, `ON`. – Gordon Linoff Sep 27 '16 at 19:25
  • The `JOIN` is a right direction. Could you please show as your query where you are getting ambiguity errors? Probably you are missing column aliases. – kpater87 Sep 27 '16 at 19:26
  • Jojning the same table is the way to go. You probably get ambiguity errors, because there are two tables in your query with the same name. You need table aliases, so you can treat the two table instances as if they were different tables. – Thorsten Kettner Sep 27 '16 at 19:31
  • Because you have started learning SQL yeterday I will also explain you what your query from the question is doing: `WHERE CUSTOMER_ZIP = CUSTOMER_ZIP` is the same like `WHERE 1=1` so it is always TRUE, therefore you are selecting all records. Then you are selecting 4 columns: `CUSTOMER_FIRST_NAME` twice and `CUSTOMER_LAST_NAME` twice of the same customer. – kpater87 Sep 27 '16 at 19:33
  • @kpater87 Have I done something wrong with the distinct command ? – Mephistofee Sep 27 '16 at 20:11
  • What do you mean by saying: it will show duplicate data? DISTINCT only removes duplicated rows. – kpater87 Sep 27 '16 at 20:12
  • @kpater87 as in I also get rows that say Mary Smith Mary Smith for example – Mephistofee Sep 27 '16 at 20:13
  • @kpater87 - will the query in the original post return data from rows where `CUSTOMER_ZIP` is null? (Hint: NO.) What does this mean for your claim, that `WHERE CUSTOMER_ZIP = CUSTOMER_ZIP` is the same as `WHERE 1=1`? –  Sep 28 '16 at 00:27

1 Answers1

1

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

Community
  • 1
  • 1
kpater87
  • 1,190
  • 12
  • 31