How to query on SQLite with relational tables?

user_15089160 0
Viewed 44

I have a table called "ASSIGNMENTS" and another table called "COURSE". My "COURSE TABLE" has a column called "limit" in which i store the limit of students that can be assigned to a COURSE.

ASSIGNMENTS

| id(PK) | course_code(FK references course.id) |

COURSE

| id(PK)| name | limit(INT) |

So basically what i have to do is return the limit of each course MINUS the amount of ASSIGNMENTS that the course has had.

So if my Course "Math" has a limit of 30 assignments, and i have 5 assignments with course_code for "Math" in my assignments table, i should return 25. How can i achieve this? i'm having a very hard time decoding this. (very new to sql)

I'm writing my code using sqlite, this is what i have so far:

SELECT DISTINCT
c.name AS 'COURSE NAME', c.limit as 'AVAILABLE'
FROM COURSE c
INNER JOIN ASSIGNMENTS i
ON c.id=i.course_code

And right there i just don't know how to make it return the limit value MINUS the amount of assignments for each Course. Any help or examples of sorts will be appreciated, thank you.

join sqlite
1 Answers

You can group by course and subtract the number of assignments from limit:

SELECT c.id, c.name, c.`limit`, 
       c.`limit` - COUNT(*) available 
FROM COURSE c INNER JOIN ASSIGNMENTS a
ON c.id = a.course_code
GROUP BY c.id, c.name

Or, with a correlated subquery:

SELECT c.*, 
       c.`limit` - (SELECT COUNT(*) FROM ASSIGNMENTS a WHERE a.course_code = c.id) available 
FROM COURSE c

See a simplified demo.

user_104988280
Related