driftdrift driftdrift - 2 months ago 7
MySQL Question

How do i query from a Join Table?

New to SQL, Assuming this is the right way to create a JOIN Table between my two main entities, do I have to hardcode insert data for the join table? How do I query from my join table? I'm using SQL Fiddle so I'm not sure if the links are being produced correctly to my foreign keys.

CREATE TABLE Organization(
`Organization_id` int NOT NULL,
PRIMARY KEY(`Organization_id`)
);

CREATE TABLE QuestionBank(
`Question_id` int NOT NULL,
`Question_text` VARCHAR(255) NOT NULL,
PRIMARY KEY(`Question_id`)
);

CREATE TABLE OrganizationQuestion(
`OrganizationQuestion_id` int NOT NULL,
`Question_id` int NOT NULL,
`Organization_id` int NOT NULL,
PRIMARY KEY(`OrganizationQuestion_id`),
FOREIGN KEY(`Question_id`) REFERENCES QuestionBank(`Question_id`),
FOREIGN KEY(`Organization_id`) REFERENCES Organization(`Organization_id`)
);

INSERT INTO Organization(`Organization_id`) VALUES(1);
INSERT INTO QuestionBank(`Question_id`, `Question_text`) VALUES(1, 'How did he perform?');

INSERT INTO OrganizationQuestion(`OrganizationQuestion_id`, `Question_id`, `Organization_id`)
VALUES(1, 1, 1);

Answer

This is your join:

select oq.OrganizationQuestion_id, 
oq.Question_id, 
oq.Organization_id, 
o.Organization_id, 
qb.Question_id, 
qb.Question_text 
from OrganizationQuestion oq 
join Organization o 
on o.Organization_id = oq.Organization_id 
join QuestionBank qb 
on qb.Question_id = oq.Question_id 
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+
| OrganizationQuestion_id | Question_id | Organization_id | Organization_id | Question_id | Question_text       |
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+
|                       1 |           1 |               1 |               1 |           1 | How did he perform? |
+-------------------------+-------------+-----------------+-----------------+-------------+---------------------+

Which is not terribly interesting because you made almost everything a 1.

Output with EXPLAIN:

+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys               | key     | key_len | ref                             | rows | Extra       |
+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | oq    | ALL    | Question_id,Organization_id | NULL    | NULL    | NULL                            |    1 | NULL        |
|  1 | SIMPLE      | o     | eq_ref | PRIMARY                     | PRIMARY | 4       | so_gibberish.oq.Organization_id |    1 | Using index |
|  1 | SIMPLE      | qb    | eq_ref | PRIMARY                     | PRIMARY | 4       | so_gibberish.oq.Question_id     |    1 | NULL        |
+----+-------------+-------+--------+-----------------------------+---------+---------+---------------------------------+------+-------------+

Remember that KEYS (Indexes) are not used in queries with small tables. It takes longer to use the KEYS than just a table scan.

To view indexes on a table:

mysql> show indexes from OrganizationQuestion;
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name        | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| organizationquestion |          0 | PRIMARY         |            1 | OrganizationQuestion_id | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| organizationquestion |          1 | Question_id     |            1 | Question_id             | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| organizationquestion |          1 | Organization_id |            1 | Organization_id         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------+------------+-----------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

See the MySQL Manual pages for SHOW INDEX and EXPLAIN

Comments