Pulling names from same table

Question

Need name for repaycustomer# column when only customer # is mapped to name.

Customer# | CustomerName| RepayCustomer#| Loan#
123       | John Doe    | 456           | 1    
456       | Jane Doe    | 456           | 2
123       | John Doe    | 123           | 3

expected results

Customer# | CustomerName| RepayCustomer#| Loan# | RepayCustomer 
123       | John Doe    | 456           | 1     | Jane Doe
456       | Jane Doe    | 456           | 2     | Jane Doe
123       | John Doe    | 123           | 3     | John Doe

I'm guessing customer_name is a column from the customer# table? And/or the Repaycustomer# table? I think what you are asking is you have two tables customer and repaycustomer. You need to do a join on those tables, and you want to know how to distinguish between the customer_name column that exists on both tables? — D.L., May 11 '17 at 21:52
no they are both on the same table. Customer and RepayCustomer are 2 different individuals. but repaycustomer is also on the customer column. — M Hernandez, May 11 '17 at 21:53
Can you give examples of the type of data in each column? Is the customer_name included with other data in the columns? — D.L., May 11 '17 at 21:54
@ D.L. I updated the question to show what I have and what I need to have. — M Hernandez, May 11 '17 at 22:52
What tables do you have? Don't you actually have no customer table that contains exactly one row for each customer? — miracle173, May 12 '17 at 05:04

score 1 · Answer 1 · answered May 11 '17 at 23:49

Presuming that each CUSTOMER# is uniquely associated to a single CUSTOMERNAME, a scalar subquery should be all that is required to generate the expected results.

Below is an example.

First create the test table:

CREATE TABLE CUSTOMER(
  CUSTOMER# NUMBER,
  CUSTOMERNAME VARCHAR2(64),
  REPAYCUSTOMER# NUMBER,
  LOAN# NUMBER
);

And load the test data:

INSERT INTO CUSTOMER VALUES (123, 'John Doe',456,1);
INSERT INTO CUSTOMER VALUES (456, 'Jane Doe',456,2);
INSERT INTO CUSTOMER VALUES (123, 'John Doe',123,3);

Then run query:

SELECT
  CUSTOMER.CUSTOMER#,
  CUSTOMER.CUSTOMERNAME,
  CUSTOMER.REPAYCUSTOMER#,
  CUSTOMER.LOAN#,
  (SELECT DISTINCT REPAYCUSTOMER.CUSTOMERNAME
   FROM CUSTOMER REPAYCUSTOMER
   WHERE REPAYCUSTOMER.CUSTOMER# = CUSTOMER.REPAYCUSTOMER# ) AS REPAYCUSTOMER
FROM CUSTOMER;

Result:

CUSTOMER#  CUSTOMERNAME  REPAYCUSTOMER#  LOAN#  REPAYCUSTOMER  
123        John Doe      456             1      Jane Doe       
456        Jane Doe      456             2      Jane Doe       
123        John Doe      123             3      John Doe

But please note if there are ambiguities in the unique association between CUSTOMER# and CUSTOMERNAME (For example if the first customer# 123 were John Doe and the second 123 wereChewbacca) this query will (and should) fail.

If the dependency between CUSTOMERNAME and CUSTOMER# are consolidated the need need for DISTINCT here is alleviated.

If possible, it might be worth considering alternative designs to the provided table--this can help make fetches like this reliable, and can help in maintaining the integrity of the data (as well as in constraining the hierarchical relationships between rows).

I think you can get around the problem with distinct by using row_number(), although your point about consolidation, of course, still holds. — D.L., May 12 '17 at 01:58

score 0 · Answer 2 · edited May 23 '17 at 11:47

The answer from @alexgibbs can be improved a little by limiting the result using row_number() to handle the "ambiguities" he mentions where there are multiple rows with the same CUSTOMER# but with different customer names. In the case where there are different names, the first alphabetically would always be chosen.

SELECT
  CUSTOMER.CUSTOMER#,
  CUSTOMER.CUSTOMERNAME,
  CUSTOMER.REPAYCUSTOMER#,
  CUSTOMER.LOAN#,
  (SELECT RC2.REPAYCUSTOMER FROM
      (SELECT CUSTOMERNAME AS REPAYCUSTOMER,
              ROW_NUMBER() AS SEQUENCE_NUMBER
         FROM CUSTOMER RC
        WHERE RC.CUSTOMER# = CUSTOMER.REPAYCUSTOMER#
        ORDER BY RC.CUSTOMERNAME) RC2
    WHERE RC2.SEQUENCE_NUMBER = 1)
FROM CUSTOMER;

See here for why you can't simply use rownum: How to use Oracle ORDER BY and ROWNUM correctly?

Thanks Guys it worked. (Select Distinct Customer.Customer_Name From CUSTOMER Where Customer.Customer_Number = Psr_Cis And Rownum=1 ) As "PSR_NAME", — M Hernandez, May 12 '17 at 21:47

score 0 · Answer 3 · answered May 11 '17 at 23:44

0

Assuming the name of your table is Customer:

SELECT C1.Customer#, C1.CustomerName, C1.RepayCustomer#, C1.Loan#,
C2.CustomerName
FROM Customer C1, (SELECT Customer#, CustomerName
                   FROM CUSTOMER) C2
WHERE C1.RepayCustomer#=C2.Customer#;

answered May 11 '17 at 23:44

alejandrogiron

544
2
7
18

I have a lot of tables joined how can i incorporate this ? – M Hernandez Jul 24 '17 at 19:59

Pulling names from same table

3 Answers3