Fahad Uddin Fahad Uddin - 7 months ago 9
SQL Question

Inner join with of 3 tables

I have three tables which I am trying to join using the sample in this question. However I am getting below error.

Error:

#1054 - Unknown column 'referrer.Referrer_Name' in 'field list'


Code:

SELECT pupils.Pupils_Surname,
pupils.Pupils_FirstName,
referrer.Referrer_Name,
progress_track.test_1,
progress_track.test_2,
progress_track.test_3,
progress_track.test_4,
progress_track.test_5,
progress_track.test_6,
progress_track.test_7,
progress_track.test_8,
progress_track.test_9,
progress_track.test_10
FROM pupils
INNER JOIN progress_track ON progress_track.Progress_Report_Pupils_ID=pupils.Pupils_ID;
INNER JOIN referrer ON pupils.Pupils_Referrer, referrer.Referrer_ID ;

Answer

First let me highlight the faults in your query:

1) The query ended unexpectedly for a typo

2) The last INNER JOIN contained syntax error.

I've highlighted the faults in the image given below:

enter image description here

Remedy:

SELECT
    pupils.Pupils_Surname,
    pupils.Pupils_FirstName,
    referrer.Referrer_Name,
    progress_track.test_1,
    progress_track.test_2,
    progress_track.test_3,
    progress_track.test_4,
    progress_track.test_5,
    progress_track.test_6,
    progress_track.test_7,
    progress_track.test_8,
    progress_track.test_9,
    progress_track.test_10
FROM
    pupils
INNER JOIN progress_track ON progress_track.Progress_Report_Pupils_ID = pupils.Pupils_ID
INNER JOIN referrer ON pupils.Pupils_Referrer = referrer.Referrer_ID;

In response to your comment:

Could you please post this as an answer. It worked but it still did not show any data from the referrer table

  • If the join condition doesn't meet then there's no way you can see data unless you use LEFT JOIN. Please check your data first.
  • Please ensure that you have data which meets these two conditions : progress_track.Progress_Report_Pupils_ID = pupils.Pupils_ID referrer.Referrer_ID = pupils.Pupils_Referrer