MySQL How can I make a better query to connect two tables through my bridge table?

Question

I have these tables:

words:

+----+------+
| ID | DATA |
+----+------+
|  1 | jo   |
|  2 | yes  |
|  3 | jupp |
|  4 | yeah |
|  5 | jepp |
|  6 | joah |
|  7 | ne   |
|  8 | nee  |
|  9 | no   |
| 10 | nope |
| 11 | nah  |
+----+------+

statements:

+----+------+
| ID | DATA |
+----+------+
|  1 | ja   |
|  2 | nein |
+----+------+

and a bridge table that connects the words from table "words" with the DATA from table "statements":

bridge:

+--------------+---------+
| ID_statement | ID_word |
+--------------+---------+
|            1 |       1 |
|            1 |       2 |
|            1 |       3 |
|            1 |       4 |
|            1 |       6 |
|            2 |       8 |
|            2 |       9 |
+--------------+---------+

I wanted to get a SELECT QUERY to get all words associated with the statement "ja".

this query does the job, but seems more complicated than it should be:

SELECT words.DATA FROM words 
 JOIN bridge ON words.ID = bridge.ID_word 
 JOIN statements ON statements.ID = bridge.ID_statement 
 WHERE statements.ID = (
 SELECT ID FROM statements WHERE statements.DATA = "ja"
 );

Intuition tells me that I am doing that far to comlicated, but I can't figure out where the complication lies. awkward.

Answer 1

I like to name the primary keys the same as the columns that reference them. So in your example, in the words table, you'd name the primary key ID_word. In the statements table, you'd name the primary key ID_statement.

The advantage is that you can make your SQL join a little more concise with the USING(...) syntax. This syntax assumes there's a column by that name in both tables of a join, and you want the join to match where the column is equal to the same column in the other table.

SELECT words.DATA FROM words 
 JOIN bridge USING (ID_word) 
 JOIN statements USING (ID_statement) 
 WHERE statements.DATA = 'ja';

Also you don't need to run a subquery in your example. The rows in statement matching the IDs from the rows in statement where DATA='ja' are the same set as the rows in statement where DATA='ja'.

how would one describe the relationship here? one-to-many?

The relationship modeled by a bridge table is a many-to-many relationship. The specific data in your example doesn't show it, but it's possible that many different statements could reference the same word. What you do show is that each statement can reference many words.