MySQL - Get records from one table based on count of another table's column

68
June 29, 2018, at 11:20 AM

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)

Answer 2
select distinct students.name from students join documents on 
students.user_id=documents.user_id where documents.user_id is No
READ ALSO
PHP - populate a select drop down from MySQL DB and have that field auto-populated in a form

PHP - populate a select drop down from MySQL DB and have that field auto-populated in a form

I am trying to accomplish two things with the code belowFirstly I want my select options to be populated form my database

77
Laravel Administrator Localization

Laravel Administrator Localization

I'm working with Laravel 53 and I am unable to use some of the public packages which are offering localization messages in the database

146
hiding files with php

hiding files with php

I can't wrap my head around this, and it is SO frustratingI have no idea how I'd be solving my problem

98
PHP INI Array with Variable Variable

PHP INI Array with Variable Variable

I have a name value pair in a settingsini:

88