CODEWITHSUNDEEP
mysqldebuggingleft-join
Clifford Yeti Mapp
Clifford Yeti Mapp

Reputation: 189

Multiple Left Joins in MySQL

I seem to be having some issues getting my multi left join to give me the correct results, it gives me the id just fine however i cannot seem to get the link to give me the resulting question instead of the ID.

Code below.

SELECT 
  a.id as id,
  a.clientid as clientid,
  a.comp_id as compid,
  a.title as title,
  a.firstname as firstname,
  a.lastname as lastname,
  a.countrycode as countrycode,
  a.mobile as mobile,

  a.question1_answer as question1_answer,
  a.question2_answer as question2_answer,
  a.question3_answer as question3_answer,

  a.timestamp as timestamp, 
  b.comp_name as comp_name,
  b.comp_id as comp_id,

  a.question1 as question1,
  a.question2 as question2,
  a.question3 as question3

FROM 
  competition_entries AS a 
LEFT JOIN
  competition as b
ON 
  a.comp_id = b.id
LEFT JOIN
  questions as q
ON 
  a.question1 = q.question_id 
AND
  a.question2 = q.question_id
AND
  a.question3 = q.question_id   
WHERE 
a.comp_id = '$download_id'

However each time i call question1 / question2 / question3 - the only results shown are the number values that are corresponding to their ID within the other table.

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need to join to the question table three times, once for each id you have:

SELECT a.id as id, a.clientid as clientid, a.comp_id as compid, a.title as title,
       a.firstname as firstname, a.lastname as lastname, a.countrycode as countrycode,
       a.mobile as mobile,
       a.question1_answer as question1_answer,
       a.question2_answer as question2_answer,
       a.question3_answer as question3_answer,
       a.timestamp as timestamp, 
       b.comp_name as comp_name,
       b.comp_id as comp_id,  
       a.question1 as question1id,
       a.question2 as question2id,
       a.question3 as question3id,
       q1.question as question1,
       q2.question as question2,
       q3.question as question3

FROM competition_entries a LEFT JOIN
     competition as b
     ON a.comp_id = b.id LEFT JOIN
     questions as q1
     ON a.question1 = q1.question_id left join
     questions q2
     on a.question2 = q2.question_id left join
     questions q3
     on a.question3 = q3.question_id   
WHERE a.comp_id = '$download_id'

Upvotes: 2

Related Questions