Inner join with of 3 tables

Question

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 ;

There's a semicolon after the first inner join. So your query missed this line (`INNER JOIN referrer ON pupils.Pupils_Referrer, referrer.Referrer_ID ;`) — 1000111, Apr 25 '16 at 07:31
Please make sure a column name: Referrer_Name in the "referrer" table is exists or not. I thourght you have to remove the semi colon for the first inner join reference. — Ramesh Moorthy, Apr 25 '16 at 07:32
INNER JOIN referrer ON pupils.Pupils_Referrer, referrer.Referrer_ID ; It Should be INNER JOIN referrer ON pupils.Referrer_ID=referrer.Referrer_ID if pupils table have Referrer_ID — Abhishekkumar, Apr 25 '16 at 07:33
@1000111: I removed the semicolor. The code still shows same error. — , Apr 25 '16 at 07:34
Change the last line too ` INNER JOIN referrer ON pupils.Pupils_Referrer = referrer.Referrer_ID ;` — 1000111, Apr 25 '16 at 07:34
@1000111: Could you please post this as an answer. It worked but it still did not show any data from the referrer table. — , Apr 25 '16 at 07:36
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. — 1000111, Apr 25 '16 at 07:39
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` — 1000111, Apr 25 '16 at 07:42
@1000111: Your comment " Change the last line too 'INNER JOIN referrer ON pupils.Pupils_Referrer = referrer.Referrer_ID ;' worked. Could you please post this as an answer so I can upvote and accept the answer. Thanks. — , Apr 25 '16 at 07:44
Just delete the question. Typos are off topic for stack exchange — Strawberry, Apr 25 '16 at 08:32
@Strawberry: I am likely not to do that since deleting a lot of questions starts the question feature to be blocked. — , Apr 25 '16 at 08:35

Ramesh Moorthy · Answer 1 · 2016-04-25T08:53:03.577

Please remove the semicolon at the end of the first inner join statement. Because, the query is tried to terminate the statement after the first semicolon of the query itself. So, this is the reason for the column Referrer_Name is not referring the table:"referrer".

original:

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 ;

Use this:

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 ;

score 1 · Accepted Answer · answered Apr 25 '16 at 07:51

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:

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

Inner join with of 3 tables

2 Answers2