How do i query from a Join Table?

Question

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);

The question is, OrganizationQuestion is a join table between two tables. Is inserting data like the way I did above correct? Because I did a SELECT * from the join table and it returned my hard coded insert but it doesnt show any signs of awareness of the keys when I view the execution plan. I was wondering if this is a caveat of sqlfiddle or if im not doing it correctly. — driftdrift, Sep 05 '16 at 21:19
You can see the keys used by running EXPLAIN (who knows about with sqlfiddle). But with 1 row in each table, the keys are not used. — Drew, Sep 05 '16 at 21:23

score 1 · Accepted Answer · edited May 23 '17 at 12:32

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

Edit 2 completely different question

To disallow content during an INSERT (say, 2 FK id's in one table being the same such a Mail 'sender' and 'recipient')

See the following Answer for generating a

signal sqlstate '45000';

Sorry I just started using SQL so im not really following.. when you refer to my join, is that automatically being done by what I have above by creating the join table and inserting something into it? — driftdrift, Sep 05 '16 at 21:30
No, you created 3 tables and inserted data in them. I used the one with the most detail (alias `oq`) and performed a `JOIN` from it back up to the other 2 tables. It was pretty intuitive what you were doing with your 3 tables, so I followed what I thought was common sense and did a `JOIN` — Drew, Sep 05 '16 at 21:32
So the `FROM` plus the `JOIN ON` (twice) brought the 3 together as a join of 3 tables. — Drew, Sep 05 '16 at 21:35
ok I understand. So when I create the join table, the two foreign keys are used only as a reference when an actual join query is being performed, and they get pointed to the properties of their reference table that is selected in the query. Statically, they do not do anything — driftdrift, Sep 05 '16 at 21:42
So your 3rd table is like a [Junction Table](http://stackoverflow.com/a/32620163), a many-to-many, an Intersect table. You can think of it as a Join table if you want. Just don't confuse it with an actual join operation or query. — Drew, Sep 05 '16 at 21:43
Btw feel free to ask us random db questions in the [Campaigns](http://chat.stackoverflow.com/rooms/95290) — Drew, Sep 05 '16 at 21:46
The 2 foreign keys are only there for Referential Integrity because you were wise enough to care enough about your data. So that when an Insert or Update happens, you have rules enforced. So let us call that a Junction table so we don't confuse it with an actual Join that will happen later. — Drew, Sep 05 '16 at 21:48
Later, I did a actual Join. I could have tried to join on a month(someDate) or whatever, if it was possible. But in the case of the Join query, it used the same obvious keys that made up the FK referential integrity — Drew, Sep 05 '16 at 21:50
Ok I have another question. If I have two entities, say a Civilian, and a Mail Entity. I want to not allow a Citizen to Mail himself. Normally I'd set up two foreign keys in Mail, a recipient_id and a sender_id that are equal to the primary_key in Citizen. But they can't be the same, how do i make this constraint? — driftdrift, Sep 05 '16 at 23:27

How do i query from a Join Table?

1 Answers1

Edit 2 completely different question