Software >> Services >> RDBMS >> MySQL >> How to display columns from 2 tables

MySQL example for quiz database

quiz anwers table : t_answers

+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| aid      | int(11) | NO   | PRI | NULL    | auto_increment |
| qid      | int(11) | NO   |     | NULL    |                |
| q_option | text    | NO   |     | NULL    |                |
| uid      | int(11) | NO   |     | NULL    |                |
| score_u  | float   | NO   |     | 0       |                |
| rid      | int(11) | NO   |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

quiz question bank table : t_qbank

+---------------------+--------------+------+-----+-------------------------------+----------------+
| Field               | Type         | Null | Key | Default                       | Extra          |
+---------------------+--------------+------+-----+-------------------------------+----------------+
| qid                 | int(11)      | NO   | PRI | NULL                          | auto_increment |
| question_type       | varchar(100) | NO   |     | Multiple Choice Single Answer |                |
| question            | text         | NO   |     | NULL                          |                |
| description         | text         | NO   |     | NULL                          |                |
| cid                 | int(11)      | NO   |     | NULL                          |                |
| lid                 | int(11)      | NO   |     | NULL                          |                |
| no_time_served      | int(11)      | NO   |     | 0                             |                |
| no_time_corrected   | int(11)      | NO   |     | 0                             |                |
| no_time_incorrected | int(11)      | NO   |     | 0                             |                |
| no_time_unattempted | int(11)      | NO   |     | 0                             |                |
+---------------------+--------------+------+-----+-------------------------------+----------------

To list the columns from both answers and questions table from the results of the latest quiz taken

First determine the latest results id (rid) and then use that as filter for the answers table and questions table

USE quizdb;
SET @myrid=(select rid from t_answers order by aid desc limit 1);
SELECT t_answers.rid, t_answers.qid,left(t_qbank.question,25),t_answers.score_u
FROM t_answers,t_qbank
WHERE
t_answers.qid=t_qbank.qid
AND
t_answers.rid=@myrid order by t_answers.qid
ASC

Result
+-----+-----+---------------------------------+---------+
| rid | qid | left(t_qbank.question,25) | score_u |
+-----+-----+---------------------------------+---------+
| 242 | 659 | <p>[ET37-541]&nbsp; An or       |       1 |
| 242 | 660 | <p>[ET37-542]&nbsp; A com       |       1 |
| 242 | 661 | <p>[ET37-543]&nbsp; An or       |       1 |
| 242 | 662 | <p>[ET37-544]&nbsp; A Sys       |       1 |
| 242 | 663 | <p>[ET37-545]&nbsp; A new       |     0.5 |
| 242 | 663 | <p>[ET37-545]&nbsp; A new       |     0.5 |
| 242 | 664 | <p>[ET37-546]&nbsp; A web       |       1 |
| 242 | 665 | <p>[ET37-547]&nbsp; While       |     0.5 |
| 242 | 665 | <p>[ET37-547]&nbsp; While       |     0.5 |
| 242 | 666 | <p>[ET37-548]&nbsp; An ex       |       1 |
| 242 | 667 | <p>[ET37-549]&nbsp; A Sys       |       1 |
| 242 | 668 | <p>[ET37-550]&nbsp; An ap       |       1 |
| 242 | 669 | <p>[ET37-551]&nbsp; An ap       |       1 |
| 242 | 670 | <p>[ET37-552]&nbsp; Malic       |       1 |
| 242 | 671 | <p>[ET37-553]&nbsp; A Sys       |       1 |
| 242 | 672 | <p>[ET37-554]&nbsp; A Sys       |       1 |
| 242 | 673 | <p>[ET37-555]&nbsp; With        |       1 |
+-----+-----+---------------------------------+---------+