Recursive sql query n to n in SQLite

Question

I have a database table like that

Table student
_______________
id_student int (PK)
student_name VARCHAR

and I have a recursive loop: A student can oversee many students, and a student can be overseen by many students so a new table:

Table oversee
________________
id_student pk, fk 
id_overseen pk, fk
date date

the problem is that I want to get the list that I have I made an sql query:

with 
sr1 as ( select s.student_name as over from student s, oversee o where o.id_student = s.id_student),
sr2 as (select s.student_name as overseen from student s, oversee o where o.id_overseen = s.id_student)
select distinct * from sr1, sr2;

the problem is that's the query returns the wrong answers I mean if we have two lines in the table, it will return 4 lines. I want to get every student with his overseen: Student | overseen. Someone has any idea please? Thanks.

It is unclear what you want query to return. You sure didn't write any recursive logic anywhere. — Andreas
– Andreas, Commented Jul 10, 2018 at 14:43
SQL is a language but different databases implement parts of the language differently, so what database are you using (re-tag your question appropriately). — Sentinel
– Sentinel, Commented Jul 10, 2018 at 14:45
@Andreas I want to get a table with student | overseen | date — Dev web
– Dev web, Commented Jul 10, 2018 at 14:45
You should provide sample data and the expected results along with the query you've already provided. You query has a cross product (theta join) between sr1 and sr2. — Sentinel
– Sentinel, Commented Jul 10, 2018 at 14:47
Don't clarify question in comments. Edit the question to clarify it. Delete the comment. — Andreas
– Andreas, Commented Jul 10, 2018 at 14:50

Andreas · Accepted Answer · 2018-07-10 15:12:50Z

2

I want to get a table with student | overseen | date

SELECT s.student_name AS student
     , s2.student_name AS overseen
     , oversee.date
  FROM student s
  JOIN oversee ON oversee.id_student = s.id_student
  JOIN student s2 ON s2.id_student = oversee.id_overseen

edited Jul 10, 2018 at 15:12

answered Jul 10, 2018 at 14:49

Andreas

160k13 gold badges164 silver badges262 bronze badges

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

2 Comments

Dev web Over a year ago

I don't think it's working, that returns every student in the database

Andreas Over a year ago

@Devweb If you don't want the students that are not overseen, remove the two LEFT keywords.

Collectives™ on Stack Overflow

Recursive sql query n to n in SQLite

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