enter image description here

There is such a database with tables.

Suppose there are 3 sections (sections), each section has a different number of lessons (lessons), for each account there is a report (reports) for each lesson. The checked state can be changed only by a certain group of users.

Essence of the question:

There is some kind of account id. We must get a sample of the sections that he passed. Let's say if the user with id = 100 has passed all the lessons of section 1 (passed: reports.checked = 1), and 2 lessons from 5 to 2 sections, then the query should return data only for 1 section.

Request to the studio please

  • write an example with the data - Roman C

1 answer 1

it seems everything is simple: inside we find sections in which there are no marked lessons. outside we find sections for which there is no section with missing ones marked. double denial)

select * from sections where not exists ( select top 1 * from sections as s join lessons as l on (s.Id = l.section_id) where s.Id = sections.Id and not exists ( select * from reports as r where l.id = r.lesson_id and r.checked = 1 ) )

or with explicitly separated logical parts of the request

 with notChecked as ( select s.id from sections as s join lessons as l on (s.Id = l.section_id) where not exists ( select * from reports as r where l.id = r.lesson_id and r.checked = 1 ) ) select * from sections where not exists ( select top 1 * from notChecked where sections.id = notChecked.id )