How to self join with nulls postgresql

Question

I want to show the names of the customers along with the names of those who referred them. I thought I was on the right track but the result is messed up. I tried conditions ReferredBy NOT NULL in WHERE clause, ReferredBy NOT NULL in ON clause - no luck. I would appreciate any help! sqlfiddle

CREATE TABLE Customers(
          Id int NOT NULL,
          Name varchar(50) NOT NULL,
          ReferredBy int  REFERENCES Customers(Id),
          PRIMARY KEY (Id)
);

INSERT INTO Customers VALUES
(11, 'Peter', 22),
(22, 'Ariel', NULL),
(33, 'Tom', 11);

My approach:

 SELECT c.Id, c.Name, c.ReferredBy, n.Name as ReferredBy_name
 FROM Customers c
 LEFT JOIN Customers n
 ON c.Id = n.ReferredBy

Desired Output:

Sample data and desired results is helpful. – Gordon Linoff May 16 '21 at 19:25 — Gordon Linoff, May 16 '21 at 19:25

score 1 · Accepted Answer · answered May 16 '21 at 19:26

1

I think the JOIN conditions have the tables inverted:

SELECT c.Id, c.Name, c.ReferredBy, n.Name as ReferredBy_name
FROM Customers c LEFT JOIN
     Customers n
     ON n.Id = c.ReferredBy ;

answered May 16 '21 at 19:26

Gordon Linoff

1,242,037
58
646
786

1

Unbelievable! Thank you! – eyei May 16 '21 at 19:29

How to self join with nulls postgresql

1 Answers1