-1

enter image description here

I have a table as above. How can I use self join to get the output sequentially as below?

  1. Preheat an oven to 220 degrees C.
  2. Peel four potatoes.
  3. Cut potatoes into slices.
  4. Toss sliced potatoes with oil.
  5. Bake in the preheated oven for 20 minutes.
  6. Season the hot slices with salt and pepper.
select A.content
from recipe A
left join 
(select 
CASE WHEN previous_id IS NULL THEN 0 ELSE previous_id END previous_id, 
CASE WHEN next_id IS NULL THEN 0 ELSE next_id END next_id 
from recipe order by previous_id) B
on B.next_id = A.id;

How do I use previous_id here?

DDL statements for recreating the table 

create table recipes (id int, content varchar(50) , previous_id int, next_id int);
insert into recipes (id, content, previous_id, next_id) values (1, 'Preheat an oven to 220 degrees C.', NULL, 2);
insert into recipes (id, content, previous_id, next_id) values (2, 'Peel four potatoes.', 1, 4);
insert into recipes (id, content, previous_id, next_id) values (3, 'Toss sliced potatoes with oil.', 4, 6);
insert into recipes (id, content, previous_id, next_id) values (4, 'Cut potatoes into slices.', 2, 3);
insert into recipes (id, content, previous_id, next_id) values (5, 'Season the hot slices with salt and pepper.', 6, NULL);
insert into recipes (id, content, previous_id, next_id) values (6, 'Bake in the preheated oven for 20 minutes.', 3, 5);
Sneha L
  • 1
  • 1
  • 1
    And read [How To Ask](https://stackoverflow.com/help/how-to-ask) and [How To Ask SQL](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) – Dale K May 13 '20 at 02:00
  • 1
    And [Asking Homework Questions](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions) – Dale K May 13 '20 at 02:07
  • It's a great starting point but doesn't quite answer my question. – Sneha L May 13 '20 at 02:32
  • 1
    @DaleK none of those solutions are MySQL and they all involve recursion, which isn't available in MySQL until version 8 – Nick May 13 '20 at 03:04
  • 1
    https://stackoverflow.com/questions/675117/fetching-linked-list-in-mysql-database – Dale K May 13 '20 at 03:07

2 Answers2

1

You dont need to work with JOIN. You only need to SELECT all data from table (if this is only data in table) and ORDER BY previous_id. ORDER BY always put NULL values on top (if you don't order it by DESC), and after that you will get steps in ascending order by previous_id.

SELECT * FROM recipes ORDER BY previous_id

Look at this demo.

bjeftic
  • 66
  • 4
  • Ordering by previous_id would give us "Bake in the preheated oven for 20 minutes." as the fourth row, which is not our expected answer because "Toss sliced potatoes with oil." is our expected answer in the 4th row. – Sneha L May 13 '20 at 07:56
  • You need to edit previous_id for "Bake in the preheated oven for 20 minutes." to 4. Reorder column previous_id as: NULL, 1, 3, 2, 5, 4 – bjeftic May 13 '20 at 08:42
0

I have solved this problem using recursive query. It will give the appropriate desired output. Basic idea is to consider this problem as a Doubly Linked List, where each item (or "step" from the table) in content has a previous and a next pointer. We just have to iterate over this doubly linked list. We can perform recursion for that in SQL,

Here the MySQL Code for this:

 WITH RECURSIVE item(id, previous_id, content,  next_id) AS(
 SELECT id, previous_id, content, next_id FROM recipes WHERE previous_id IS NULL
 UNION ALL
 SELECT recipes.id, recipes.previous_id, recipes.content, recipes.next_id FROM item INNER JOIN recipes ON item.next_id=recipes.id
 )
 SELECT * FROM item;

You will get an output like this (Please note, here id corresponds to the id of the "steps" mentioned in the question):

code output

-- Hope it helps! Cheers!

Pranav Soni
  • 11
  • 3