Question about combining data from two tables with many to many relationship

Could anyone please explain (or link t开发者_运维问答o a good tutorial) on how to combine data from two tables which is connected with a 'link-table'?

• One table contains question
• One table contains categories
• One table contains question_categories

Each question belongs to many categories, and each category can have many questions.

The link table contain two id's, categoryID and questionID(they are primary keys in the table) with a foreign key to category and question table.

But I do not completely understand how I can write an SQL select that for instance displays what kind of categories question with ID 2 belongs to.

And thorough explanation would be MUCH appreciated!

Thanks in advance.

You use the JOIN clause to connect the tables.

SELECT Q.* 
FROM question Q
  INNER JOIN question_categories QC
    ON q.questionId = QC.questionID
WHERE QC.categoryID = 2

To break it down:

SELECT Q.*  -- Get all fields from the alias Q (though you should specify fields)
FROM question Q -- From the question table, aliased Q
  INNER JOIN question_categories QC -- Join on the question_categories table (QC)
    ON q.questionId = QC.questionID -- Using the questionIds on both tables as join criteria
WHERE QC.categoryID = 2 -- constrain to only categoryId of 2

Edit (example for categories by questionId) as requested in comments:

SELECT C.* 
FROM category C
  INNER JOIN question_categories QC
    ON C.categoryId = QC.categoryID
WHERE QC.questionID = 1