Incorrect recursion query?

Question

Perhaps I have this wrong but I thought this query would use recursion however I can't seem to write it correctly. Here is the sample data:

DECLARE @Friends TABLE
(
[person_id] [int] NOT NULL,
[first_name] [varchar](50) NOT NULL,
[favorite_color] [varchar](50) NOT NULL,
[best_friend] [varchar](50) NOT NULL
)

INSERT @Friends VALUES (1, 'John', 'blue', 'Mark')
INSERT @Friends VALUES (2, 'Mark', 'green', 'Sally')
INSERT @Friends VALUES (3, 'David', 'purple', 'John')
INSERT @Friends VALUES (4, 'Sally', 'red', 'Rose')
INSERT @Friends VALUES (5, 'Stephanie', 'blue', 'Rose')
INSERT @Friends VALUES (6, 'Rose', 'yellow', 'David')

Now I need to list each person's name in first column then their BEST FRIENDS favorite color in the second.

My thought was to use a cte and the initialization query would get the list of names and the recursion would get their best friends color.

However now I am unsure how to write the recursion part to find the best friends color?

Not looking for anyone to do my homework just trying to get headed in the right direction.

TIA

If that is all you need, you don't need to use recursion. Is using recursion part of the assignment? — SWeko
– SWeko, Commented Aug 29, 2013 at 10:55
NO!! I was just trying to use the best possible approach. I could do it with a sub-query easy enoughg. — GPGVM
– GPGVM, Commented Aug 29, 2013 at 10:59

Nenad Zivkovic · Accepted Answer · 2013-08-29 11:04:47Z

4

You don't need recursion, you don't need sub-query. All you need is simple join:

SELECT f.*, bf.favorite_color AS BF_favorite_color
FROM @Friends f
LEFT JOIN @Friends bf ON f.best_friend = bf.first_name

SQLFiddle DEMO

answered Aug 29, 2013 at 11:04

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

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

2 Comments

GPGVM Over a year ago

RIGHT!! because the left join will match with records from right table. I was definitely over thinking this one.

Sam Over a year ago

@user1278561 No need to use LEFT or RIGHT join. Since your best_friend column is NOT NULL, you can use an (INNER) JOIN. LEFT/RIGHT JOIN are for when you want to show records from one of the tables even if they have no matching records in the table you are joining with.

Collectives™ on Stack Overflow

Incorrect recursion query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related