Unknown column 'xxx' in 'on clause'

  • A+
Category:Languages

I have an SQL query that looks as follows:

SELECT      a.id user_id, a.type, a.email_address,      a.name_first, a.name_last, languages.title as language,     b.created_at purchased_on, b.expires_at watchable_until,     c.title,     d.title topic,     e.title category,     f.subject, f.grade FROM users a,      user_video_purchases b,      videos c,      gradesubject_category_topics d,      gradesubject_categories e,      gradesubjects f JOIN languages ON            users.language_preferred_id = languages.id WHERE a.id = b.user_id       AND b.video_id = c.id       AND c.gradesubject_category_topic_id = d.id       AND d.gradesubject_category_id = e.id       AND e.gradesubject_id = f.id ORDER BY purchased_on DESC; 

This query returns the follow error message:

Unknown column 'users.language_preferred_id' in 'on clause'

The columns exists in the users table and as far as I know the JOIN is correct.

Can someone please point out where I might be going wrong?

 


You are mixing old school and modern join syntax. You should be using only the latter:

SELECT a.id user_id, a.type, a.email_address, a.name_first, a.name_last,     l.title as language,     b.created_at purchased_on,     b.expires_at watchable_until,     c.title,     d.title topic,     e.title category,     f.subject, f.grade FROM users a INNER JOIN user_video_purchases b     ON a.id = b.user_id INNER JOIN videos c     ON b.video_id = c.id INNER JOIN gradesubject_category_topics d     ON c.gradesubject_category_topic_id = d.id INNER JOIN gradesubject_categories e     ON d.gradesubject_category_id = e.id INNER JOIN gradesubjects f     ON e.gradesubject_id = f.id INNER JOIN languages l     ON a.language_preferred_id = l.id ORDER BY     purchased_on DESC; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: