MySQL查询检查登录用户是否有权访问内容？ （内容属于用户注册的类）

Question

I have a theoretical issue, that I need to translate it to a database query in MySQL.

I have students that can be enrolled into a class. Each class have some class lectures (content). Only students enrolled in the class should be able to access the lectures assigned to that class. Each lecture could be assigned to N classes.

I have several tables:

USERS table with the userID field.

CONTENT table with contentID field.

CLASS table with the classID field.

USERS-CLASSES table with the userID and the classID fields

CONTENTS-CLASSES table with the classID and contentID fields.

I have no idea on how the query should actually be formulated to check that the user ID of the logged in user is connected with the classID of the class to which the lecture that the user wants to read belongs to (the content).

Any clue will be greatly appreciated.

Answer 1

This can be completed with a simple INNER JOIN

SELECT 1 FROM USERS-CLASSES uc
INNER JOIN CONTENTS-CLASSES cc ON cc.classID = uc.classID
WHERE uc.userID = {userID}

If the query returns 1, they're authorized to view that content.

If you want to know for a userID AND classID, simply add it to your where.

SELECT 1 FROM USERS-CLASSES uc
INNER JOIN CONTENTS-CLASSES cc ON cc.classID = uc.classID
WHERE uc.userID = {userID}
AND uc.classID = {classID}