两个表循环多次的结果集

Question

I have 3 tables from which I'm pulling data

Table 1 : assignment_status

primary_key   
test_group_id | s_id | date_taken | status
q1            | 123  | 2012-08-13 | completed

Table 2: question_bank

 foreign key    unique key |
 group_id     | q_id       | question
 q1           | 1          | What is your name?
 q1           | 2          | Where were you born?

Table 3: assignment_answers

foreign key  
g_id         | q_id | s_id  | s_answer
q1           | 1    |  123  | Mark Johnson
q1           | 2    |  123  | Florida 

Now I'm attempting to display the answer for it's corresponding question. The problem I'm facing is that the result set returned is looping multiple times.

For example, if question_bank and assignment_answers have 2 records each the result set has 4 records; if both have 4 records each the result set has 16 records. So the number is getting squared. This makes me sure that I'm pulling it in a wrong manner. Below is my query that I'm using:

SELECT 
    * 
FROM 
    assignment_status s, 
    question_bank b, 
    assignment_answers a
WHERE 
    s.test_group_id = b.group_id = a.g_id = 'q1'

I also tried

SELECT 
    * 
FROM 
    question_bank b, 
    assignment_answers a
WHERE 
    b.group_id = a.g_id
    AND b.group_id = 'q1'
    AND a.g_id = 'q1'

Both queries result in same multiple rows being repeated.

Any suggestions, ideas, thoughts?

Thanks in advance.

Answer 1

I think you are missing a condition for question_bank b, assignment_answers a

SELECT 
    * 
FROM 
    assignment_status s, 
    question_bank b, 
    assignment_answers a
WHERE 
    s.test_group_id = b.group_id = a.g_id = 'q1' AND b.q_id = a_q.id