SQL joining columns of the same table-CodePudding

I need help on the following SQL query. Let's say we have table_1 with these columns:

number	Customer	list
321	4514
321		2
2	5321
2	5555

If there's a number in the list column, that indicates that is that there is a list of numbers that should refer to that list. Below is a snapshot of how the final table should look. When there's a null value in the customer field it indicates that there is a list, that list number you can find the customers on that list when the number = the list. I need to change the number to make reference to the number the list belongs to.

number	Customer	list
321	4514
321	5321	2
321	5555	2

I've tried with different joins but unsuccessful:

SELECT * 
FROM table_1 
OUTER JOIN
    (SELECT * 
     FROM TABLE_1 
     WHERE list IS NOT NULL) AS table_2 ON TABLE_1.list = table_2.list

CodePudding user response：

I guess you need to change the data (DML). Here us an example:

DROP TABLE IF EXISTS customer_list

CREATE TABLE customer_list (
    number INT,
    Customer INT,
    list INT
);

INSERT INTO customer_list (number, Customer, list)
VALUES
    (321, 4514, NULL),
    (321, NULL, 2),
    (2, 5321, NULL),
    (2, 5555, NULL);

UPDATE A
SET [number] = B.number
   ,[list] = b.list
FROM customer_list A
INNER JOIN customer_list B
    ON A.number = B.list
WHERE B.Customer IS NULL

DELETE FROM customer_list
WHERE Customer IS NULL;

SELECT *
FROM customer_list

If you need only to get the records:

SELECT B.number
      ,A.customer
      ,B.List
FROM customer_list A
INNER JOIN customer_list B
    ON A.number = B.list
WHERE B.Customer IS NULL

CodePudding user response：

You say that this is guaranteed to be one level only. So you can have 321->2, but not, say, 321->2->1.

Then, well, let's join and show the joined result:

select
  coalesce(ref.number, t1.number) as num,
  t1.customer,
  ref.list
from table_1 t1
left outer join table_1 ref on ref.list = t1.number
where t1.list is null;