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?
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