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
– Gordon Linoff

2021-05-16 19:25:50 +00:00
Commented May 16, 2021 at 19:25 — Gordon Linoff
– Gordon Linoff, Commented May 16, 2021 at 19:25

Gordon Linoff · Accepted Answer · 2021-05-16 19:26:21Z

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, 2021 at 19:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eyei Over a year ago

Unbelievable! Thank you!

Collectives™ on Stack Overflow

How to self join with nulls postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related