CODEWITHSUNDEEP
phpmysql
asprin
asprin

Reputation: 9823

Result set from two tables looping multiple times

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.

Upvotes: 1

Views: 106

Answers (2)

vyegorov
vyegorov

Reputation: 22855

You have your questions/answers identified by 2 columns, so you should use them when you join tables. Something like this might do the trick: 

SELECT as.s_id, as.date_taken, as.status,
       qb.group_id, qb.q_id, qb.question,
       aa.s_answer
  FROM assignment_status as
  JOIN question_bank qb ON as.test_group_id = qb.group_id
  JOIN assignment_answers aa ON aa.g_id = qb.group_id AND aa.q_id = qb.q_id
 WHERE as.test_group_id = 'q1';

Note: you have columns in your tables named differently, like test_group_id <--> group_id <--> g_id. This is considered a bad style, 'cos when your schema will grow to hundreds of tables, it will be difficult to write queries. So it is a good habbit to use a common naming pattern and name columns in different tables similarily.

Upvotes: 0

MimiEAM
MimiEAM

Reputation: 2630

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

Upvotes: 1

Related Questions