MySQL - 根据另一个表的列计数从一个表中获取记录

Question

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?

Answer 1

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)