MySQL - 根据另一个表的列计数从一个表中获取记录
I have a table called students and a table called documents. Each student can have any amount of document entries in the documents table, but might also have no document entries. Some of the documents might've been approved, others not.
table 1: students, table 2 documents. student PK is user_id and document PK is document_id, and document table has user_id in it as well. document table has column approved which can contain either a Yes or a No. So these two tables are linked by user_id
How can I write a MySQL query (or even better, in Active Record style for Code Igniter) that can list all students that have at least 1 unapproved document?
我有一个名为 表1： 如何编写MySQL查询（甚至更好，在Code Igniter的Active Record风格中），可以列出所有至少有1个未批准文档的学生？ p>
div> students code>的表和一个名为 documents code的表 >。 每个学生可以在 documents code>表中包含任意数量的文档条目，但也可能没有文档条目。 有些文件可能已获批准，有些则未获批准。 p>
学生 code>，表2 文档 code>。 student code> PK是 user_id code>和 document code> PK是 document_id code>， document code>表有 user_id code>也在其中。 document code>表有 approved code>列，可以包含Yes或No.所以这两个表由 user_id code> p>
链接
mysql> create table students (student_number int, student_first_name char(25), student_Last_name char(25));
Query OK, 0 rows affected (0.34 sec)
mysql> create table documents (student_number int, document_name char(25), approved bool);
Query OK, 0 rows affected (0.32 sec)
mysql> insert into students values (1,"F1","L1"),(2,"F2","L2"),(3,"F3","L3");
Query OK, 3 rows affected (0.19 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into documents values (1,"D1",0),(1,"D2",1),(3,"D3",1);
Query OK, 3 rows affected (0.16 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from students where student_number in (select student_number from documents where !approved);
+----------------+--------------------+-------------------+ | student_number | student_first_name | student_Last_name | +----------------+--------------------+-------------------+ | 1 | F1 | L1 | +----------------+--------------------+-------------------+ 1 row in set (0.02 sec)
select distinct students.name from students join documents on
students.user_id=documents.user_id where documents.user_id is No