0

I have three tables representing a many-many relationship. They are board_name, topic_name and board_topic.

Someone told me how to query these tables to get the results I want, but it currently seems like Voodoo to me. I have done some research on joins, but it is still not clear.

Could someone please explain how the query below works? For example how are the two joins related? Do they represent two sides of the join somehow? Is there a simpler way to write this query?

I have an idea that, like a butterfly, the left-hand side is joined to the center and so is the right-hand side. Maybe a picture could help me to understand?

Any help appreciated.

SELECT board_name, topic_name FROM board 
JOIN board_topic ON board.id = board_topic.board_id 
JOIN topic ON topic.id = board_topic.topic_id 
WHERE topic_name = "differentiation";
Robin Andrews
  • 3,514
  • 11
  • 43
  • 111
  • Also take a look at https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Nick Sep 02 '19 at 12:21

1 Answers1

0

There are several different kinds of joins, but the most basic one is an INNER JOIN, which is the one in your query. In your case, here's what's happening:

1) Connect the rows in the board table with the rows in the board_topic table that share the same id value. Ignore all unmatched rows.

2) Connect the rows in the topic table to the rows in the board_topic table (which have already been connected to the rows in the board table in Step 1) that share the same id value. Ignore all unmatched rows.

3) Only return rows with topic_name = "differentiation"

They may not be executed by the DB engine in that order, but logically that's what's going to happen. In the end, your result set will contain several long "rows" that are composed of all the columns in each of your three tables. You are only projecting board_name and topic_name in your SELECT but you can access any of the columns in those three tables.

Check out the links already posted in the comments. They should give a more complete picture.

ravioli
  • 3,749
  • 3
  • 14
  • 28