Unknown column 'xxx' in 'on clause'

Question

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?

it is not a good habit to mix old-style comma-separated joins and `JOIN` — Radim Bača, Oct 19 '18 at 07:16
Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Madhur Bhaiya, Oct 19 '18 at 07:17
@RadimBača I read this on another thread but I'm no too familiar with SQL. Could you elaborate? — Marcus Christiansen, Oct 19 '18 at 07:17
Explicit JOIN chains are evaluated before comma separated tables. I.e. your ON clause only has access to gradesubjects and languages columns. Re-write and do explicit JOIN everywhere! — jarlh, Oct 19 '18 at 07:18
Ah better to see it right? Make your query readable so people will have easier life to answer your question.. — dwir182, Oct 19 '18 at 07:24
a, b and c are lousy table aliases. Chose aliases that make sense, like `u` for `users`, `uvp` for `user_video_purchases` etc. — jarlh, Oct 19 '18 at 07:28
Apologies for the lousy query. I was basically just editing another developers query to add another column from another table. I'm glad I'm aware of the syntax changes now for future queries if I come across them again. Thank you for all your help. — Marcus Christiansen, Oct 19 '18 at 07:56

Tim Biegeleisen · Accepted Answer · 2018-10-19T07:22:11.507

7

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;

edited Oct 19 '18 at 07:22

answered Oct 19 '18 at 07:19

Tim Biegeleisen

502,043
27
286
360

This produces the error "Unknown column 'languages.title' in 'field list'" – Marcus Christiansen Oct 19 '18 at 07:21
1

@MarcusChristiansen Sorry, had a typo. I can't easily test my answer (nor can anyone else who is answering here). – Tim Biegeleisen Oct 19 '18 at 07:22
I understand. Thank you, however. I also learnt using explicit joins, but this was another developers code and I wasn't aware of this issue. – Marcus Christiansen Oct 19 '18 at 07:24

Fahmi · Answer 2 · 2018-10-19T07:33:27.493

4

It is better if you use explicit join like below

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 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

edited Oct 19 '18 at 07:33

answered Oct 19 '18 at 07:16

Fahmi

37,315
5
22
31

1

Explicit JOIN chains are evaluated before comma separated tables. – jarlh Oct 19 '18 at 07:19
2

I'm not sure this would fix anything. Either full table name or alias should be valid, and the addition of an alias should not make a full table invalid AFAIK. – Tim Biegeleisen Oct 19 '18 at 07:20
1

Do not use that join.. If going to answer it will be better you show the right thing to do rather than answer.. – dwir182 Oct 19 '18 at 07:20
@dwir182, you know sometimes it take time to write proper answer and I am doing that in time of your comments, btw thanks for your advice – Fahmi Oct 19 '18 at 07:22
2

@TimBiegeleisen - so far as I'm aware, if you supply an alias, the original table name *isn't* valid to use as a reference (unless there's another instance of it also in scope without an alias at all) – Damien_The_Unbeliever Oct 19 '18 at 07:22
1

@Damien_The_Unbeliever You're right :-) ... I just got nailed in my own answer because of this. – Tim Biegeleisen Oct 19 '18 at 07:23
1

Now that's answer.. +1 – dwir182 Oct 19 '18 at 07:25
@TimBiegeleisen, sure – Fahmi Oct 19 '18 at 07:33

Madhur Bhaiya · Answer 3 · 2018-10-19T07:26:34.260

Please stop using comma based Implicit joins and use Explicit Join based syntax
Use Proper Aliasing, and once a table has been aliased; you should refer to its column using the Aliasname only.
Also, in case of multi-table queries, always refer a column in conjunction with its table name/alias. This will avoid ambiguous behaviour, in case of multiple column(s) having same name(s) across the different tables.

Here is a reworked code using Explicit Joins:

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

Unknown column 'xxx' in 'on clause'

3 Answers3