英文:
SQL How to return record ID's not included in table 2 from table 1 based off of user ID in table 2
问题
在表2中，学生03已经选了01和02两门课程，但是还没有选03和04两门课程。我需要返回表1中的课程ID 03和04（学生03还没选的所有课程）。我尝试了很多查询，最后一个我尝试的是：
SELECT table1.* FROM table1
LEFT JOIN table2
ON
table1.course_ID = table2.course_ID
WHERE
table2.course_ID IS NULL
AND
table2.user_ID != 3
感谢您的帮助！
表1
| course_ID | courseName |
|---|---|
| 01 | 数学 |
| 02 | 英语 |
| 03 | 艺术 |
| 04 | 音乐 |
表2
| cert_Id | course_ID | user_ID |
|---|---|---|
| 01 | 01 | 03 |
| 02 | 02 | 03 |
英文:
I have two tables, one has course name and course ID. The second table has the ID of the students and the course ID they have taken. I need to find all the class ID’s of the classes a student hasn’t taken. For example, in table 2 student 03 has taken classes 01 and 02 but not 03 and 04 from table one. The course ID’s 03 and 04 from table one are what I need to return (all the classes student 03 hasn't taken). I've tried numerous queries and the last one I tried is:
SELECT table1.* FROM table1
LEFT JOIN table2
ON
table1.course_ID = table2.course_ID
WHERE
table2.course_ID IS NULL
AND
table2.user_ID != 3
Appreciate your help!
table 1
| course_ID | courseName |
|---|---|
| 01 | math |
| 02 | English |
| 03 | art |
| 04 | music |
table 2
| cert_Id | course_ID | user_ID |
|---|---|---|
| 01 | 01 | 03 |
| 02 | 02 | 03 |
答案1
得分: 0
select t1.* from table1 as t1
left join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3
where t2.user_ID is null;
为了更可读的查询，如果可用的话，您可以使用 EXCEPT：
select * from table1
except
select t1.* from table1 as t1
inner join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3;
英文:
select t1.* from table1 as t1
left join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3
where t2.user_ID is null;
For more readable query, you can use EXCEPT if available:
select * from table1
except
select t1.* from table1 as t1
inner join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3;
答案2
得分: 0
以下是您要翻译的内容：
根据您当前的需求，以下查询将起作用
SELECT * FROM table1 t1
WHERE course_ID
NOT IN (SELECT course_ID FROM table2 WHERE user_ID =3)
如果您在table2中有更多记录，并且需要填充多个学生的详细信息，则必须使用其他逻辑
如果您想修改查询，请使用以下方式
SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.course_ID = table2.course_ID
AND table2.user_ID = 3
WHERE table2.course_ID IS NULL
英文:
As per your current requirement below query will work
SELECT * FROM table1 t1
WHERE course_ID
NOT IN (SELECT course_ID FROM table2 WHERE user_ID =3)
If you have more records in table2 and if you need to populate more than one student's details then you have to use other logic
If you want to modify your query then use as below
SELECT table1.* FROM table1
LEFT JOIN table2 ON table1.course_ID = table2.course_ID
AND table2.user_ID = 3
WHERE table2.course_ID IS NULL
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论