Correlating columns in the same table

Question

I have a table of members of a country club that has a column indicating the member id of the member that recommended them. These IDs corresponds to the remember id (memid) column in the same table and I want to know if there is a way to make a new column that indicates the name of the recommending member based on the member id. I'm trying to use the CASE statement but I cannot figure out how to correspond the right name.

An excerpt from the table is Country Club Member Table

You wouldn't use a new column. You would just query the table with a `join`. — Gordon Linoff, Sep 20 '21 at 22:27

score 0 · Answer 1 · answered Sep 20 '21 at 22:33

Here's what the join solution would look like:

SELECT m.memid, r.memid
FROM members AS m
LEFT OUTER JOIN members AS r ON m.recommender_id = r.memid;

(I'm guessing at your table name and a column name for purposes of this example.)

Joins are fundamental to using SQL. When you want a row of the query result to include columns of more than one row, you can use a join.

Correlating columns in the same table

1 Answers1