MySQL Unknown column issue

Question

Here is my database structure

https://docs.google.com/open?id=0B9ExyO6ktYcOenZ1WlBwdlY2R3c

My SQL query looks like that

SELECT
    u.fullname,
    a.id,
    a.content,
    a.addDT,
    a.`right`,
    acr.score,
    acr.checkDT
FROM
    answers a,
    users u
LEFT JOIN `answer_chk_results` acr ON acr.aid = a.id
WHERE
    a.qid = 7
AND u.id = a.uid
GROUP BY
    a.`right` DESC

Getting error message

[Err] 1054 - Unknown column 'a.id' in 'on clause'

I'm pretty sure that a.id column exists

What am I missing?

invertedSpear · Accepted Answer · 2012-09-07 23:55:29Z

1

you can't mix join syntax with comma delimited table syntax. If you are going to left join, you need to use an inner join for the other two tables.

SELECT
    u.fullname,
    a.id,
    a.content,
    a.addDT,
    a.`right`,
    acr.score,
    acr.checkDT
FROM
    answers a
    INNER JOIN users u ON u.id = a.uid
LEFT JOIN `answer_chk_results` acr ON acr.aid = a.id
WHERE
    a.qid = 7
GROUP BY
    a.`right` DESC

This might work also, but is not guranteed:

SELECT
    u.fullname,
    a.id,
    a.content,
    a.addDT,
    a.`right`,
    acr.score,
    acr.checkDT
FROM
    (answers a,
    users u)
LEFT JOIN `answer_chk_results` acr ON acr.aid = a.id
WHERE
    a.qid = 7
AND u.id = a.uid
GROUP BY
    a.`right` DESC

answered Sep 7, 2012 at 23:55

invertedSpear

11.1k6 gold badges43 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Unknown column issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related