StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLite - Selecting records that don't have a counterpart in another table
text
Body
copied!<p>I have 4 tables:</p> <pre><code>CREATE TABLE People( id integer primary key autoincrement, 'First Name' text, 'Last Name' text, ); CREATE TABLE Courses( id integer primary key autoincrement, 'Course Name' text, 'Course Refresh Rate' integer ); CREATE TABLE [Course Enrolment]( 'Person ID' integer, 'Course ID' integer, FOREIGN KEY ('Person ID') REFERENCES People(id), FOREIGN KEY ('Course ID') REFERENCES Courses(id) ); CREATE TABLE [Course Participation]( 'Person ID' integer, 'Course ID' integer, 'Date Taken' text, FOREIGN KEY ('Person ID') REFERENCES People(id), FOREIGN KEY ('Course ID') REFERENCES Courses(id) ); </code></pre> <p>I am trying to select People who are enrolled for a course, but have either never taken the course (and therefore don't have a record in the Course Participation table) or have taken the course more than 'Course Refresh Rate' years ago. I have written a select statement, but it is not working as intended.</p> <pre><code>SELECT [First Name], [Last Name] FROM people AS p LEFT JOIN courses AS c JOIN [course enrolment] AS ce JOIN [course participation] AS cp ON p.id = ce.[Person ID] AND p.id = cp.[Person ID] AND c.id = ce.[Course ID] AND c.id = cp.[Course ID] WHERE EXISTS(SELECT * FROM [Course Enrolment] as ce_2 WHERE ce_2.[Person ID] = p.id and ce_2.[Course ID] = c.id ) AND ([date taken] < date( 'now', '-' || [course refresh rate] || ' year' ) or NOT EXISTS(SELECT * FROM [Course Participation] WHERE cp.[Person ID] = p.id and cp.[Course ID] = c.id )) ; </code></pre> <p>What have I done wrong?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload