MySQL Unknown column issue

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?

Answers


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

Need Your Help

Image upload creating problems

php mysql html5

iam new to php, i am using image upload script of php to update my logo, whenever user choose the image file the existing image file should be replaced by the new upoaded file and should be updated...

Simple Address Book using dynamic array

c++ arrays memory dynamic allocation

I am working on my school project related to "keeping contacts of customers" in C++.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.