MariaDB [toyu]> desc information_schema.INNODB_SYS_FOREIGN_COLS; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | ID | varchar(193) | NO | | NULL | | | FOR_COL_NAME | varchar(193) | NO | | NULL | | | REF_COL_NAME | varchar(193) | NO | | NULL | | | POS | int(11) unsigned | NO | | NULL | | +--------------+------------------+------+-----+---------+-------+ 4 rows in set (0.018 sec) MariaDB [toyu]> desc information_schema.INNODB_SYS_FOREIGN; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | ID | varchar(193) | NO | | NULL | | | FOR_NAME | varchar(193) | NO | | NULL | | | REF_NAME | varchar(193) | NO | | NULL | | | N_COLS | int(11) unsigned | NO | | NULL | | | TYPE | int(11) unsigned | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 5 rows in set (0.008 sec) MariaDB [toyu]> select * from information_schema.INNODB_SYS_FOREIGN LIMIT 2; +------------------------------+-----------------+----------------+--------+------+ | ID | FOR_NAME | REF_NAME | N_COLS | TYPE | +------------------------------+-----------------+----------------+--------+------+ | swim/commitment_caretaker_fk | swim/commitment | swim/caretaker | 1 | 0 | | swim/commitment_coach_fk | swim/commitment | swim/coach | 1 | 0 | +------------------------------+-----------------+----------------+--------+------+ 2 rows in set (0.003 sec) MariaDB [toyu]> select * from information_schema.REFERENTIAL_CONSTRAINTS limit 2; +--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME | +--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ | def | swim | commitment_caretaker_fk | def | swim | PRIMARY | NONE | RESTRICT | RESTRICT | commitment | caretaker | | def | swim | commitment_coach_fk | def | swim | PRIMARY | NONE | RESTRICT | RESTRICT | commitment | coach | +--------------------+-------------------+-------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ 2 rows in set (0.220 sec) MariaDB [toyu]> SELECT inf.* -> FROM information_schema.REFERENTIAL_CONSTRAINTS AS inf -> WHERE inf.CONSTRAINT_SCHEMA = 'toyu'; +--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME | +--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ | def | toyu | Class_courseId_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | class | course | | def | toyu | Class_facId_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | class | faculty | | def | toyu | Course_deptCode_fk | def | toyu | PRIMARY | NONE | RESTRICT | RESTRICT | course | department | | def | toyu | Department_schoolCode_fk | def | toyu | PRIMARY | NONE | RESTRICT | RESTRICT | department | school | | def | toyu | Enroll_classNumber_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | enroll | class | | def | toyu | Enroll_grade_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | enroll | grade | | def | toyu | Enroll_stuId_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | enroll | student | | def | toyu | Faculty_deptCode_fk | def | toyu | PRIMARY | NONE | RESTRICT | RESTRICT | faculty | department | | def | toyu | Student_advisor_fk | def | toyu | PRIMARY | NONE | RESTRICT | RESTRICT | student | faculty | | def | toyu | Student_major_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | student | department | | def | toyu | Student_minor_fk | def | toyu | PRIMARY | NONE | RESTRICT | CASCADE | student | department | +--------------------+-------------------+--------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student, -> d.deptName, -> CONCAT(f.fname, ' ', f.lname) as advisor -> FROM student AS s LEFT JOIN department AS d -> ON (s.major = d.deptCode) -> LEFT JOIN faculty AS f -> ON (s.advisor = f.facId); +-----------------+------------------------------+--------------+ | student | deptName | advisor | +-----------------+------------------------------+--------------+ | Tony Hawk | Computer Science | Paul Smith | | Mary Hawk | Computer Science | Paul Smith | | David Hawk | Computer Science | Mary Tran | | Catherine Lim | Information Technology | NULL | | Larry Johnson | Information Technology | Deborah Gump | | Linda Johnson | Computer Information Systems | Daniel Kim | | Lillian Johnson | Computer Information Systems | Andrew Byre | | Ben Zico | NULL | NULL | | Bill Ching | Arts | NULL | | Linda King | Arts | Art Allister | | Cathy Johanson | NULL | Art Allister | +-----------------+------------------------------+--------------+ 11 rows in set (0.001 sec) MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student, -> d.deptName, -> CONCAT(f.fname, ' ', f.lname) as advisor -> FROM student AS s LEFT JOIN department AS d -> ON (s.major = d.deptCode) -> LEFT JOIN faculty AS f -> ON (s.advisor = f.facId) -> WHERE d.schoolCode = 'CSE'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 10 Current database: toyu +-----------------+------------------------------+--------------+ | student | deptName | advisor | +-----------------+------------------------------+--------------+ | Linda Johnson | Computer Information Systems | Daniel Kim | | Lillian Johnson | Computer Information Systems | Andrew Byre | | Tony Hawk | Computer Science | Paul Smith | | Mary Hawk | Computer Science | Paul Smith | | David Hawk | Computer Science | Mary Tran | | Catherine Lim | Information Technology | NULL | | Larry Johnson | Information Technology | Deborah Gump | +-----------------+------------------------------+--------------+ 7 rows in set (0.059 sec) MariaDB [toyu]> SELECT CONCAT (s.fname, ' ', s.lname) AS student, -> d.deptName, -> CONCAT(f.fname, ' ', f.lname) as advisor -> FROM student AS s LEFT JOIN department AS d -> ON (s.major = d.deptCode) -> LEFT JOIN faculty AS f -> ON (s.advisor = f.facId) -> WHERE d.schoolCode = 'HSH'; +------------+----------+--------------+ | student | deptName | advisor | +------------+----------+--------------+ | Bill Ching | Arts | NULL | | Linda King | Arts | Art Allister | +------------+----------+--------------+ 2 rows in set (0.001 sec) MariaDB [toyu]> notee