CODEWITHSUNDEEP
sqlsqliterecursive-query
Dev web
Dev web

Reputation: 81

Recursive sql query n to n in SQLite

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.

Upvotes: 0

Views: 80

Answers (1)

Andreas
Andreas

Reputation: 159114

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

Upvotes: 2

Related Questions