MySQL在多个表中选择列

Question

I'm building quiz type of software and I'm pretty much lost when building complex sql queries that select columns from different tables.

There are four tables that I need to get information from.

Tables structure: http://i.imgur.com/px759LY.png

I have this sql query:

$questions = $this->db->get_results("
    SELECT * FROM
    (
        SELECT * from wp_ket_questions ".$where." ORDER BY RAND() LIMIT ".$limit."
    ) t1
    JOIN wp_ket_answers t2 ON t1.id = t2.question_id
", ARRAY_A);

This query selects columns from wp_ket_questions table and answers from wp_ket_answers table. I also use JOIN to get answers that belong to certain question.

The question is: how do I incorporate wp_ket_categories and wp_categories_questions table in this SQL query so that I could get category name that is related to certain question?

wp_ket_categories table store all possible categories that I can append to question and wp_categories_questions table store cat_id and question_id columns, somehow I need to get category name from wp_ket_categories but I'm not sure how to properly join those tables.

I would be really grateful if someone could explain me what I need to change.

Answer 1

cqusing your logic, try this. You need to link each tables

SELECT * 
from wp_ket_questions q, wp_ket_answers a ,wp_ket_categories c ,wp_categories_questions cq
where 
q.id = a.question_id 
AND c.id = cq.cat_id 
AND a.question_id = cq.question_id