Hi
I'm having problems creating queries to MySQL database with MS Query.
For example the following query:

SELECT orders.id AS id,
hours.hourssum AS hourssum
FROM
orders LEFT JOIN
(SELECT job.id AS id, sum(job.hours1) AS hourssum FROM job GROUP BY
job.id) AS hours ON orders.id=hours.id
WHERE orders.id = '981382';

gives me an error: "Unknown column hours.hourssum in 'field list'".
Same query works in MySQL Query Browser just fine. I know that the
query can be done a bit differently so it works in MS Query, but this
query is just an example of the problem I'm having.

The problem is that MS Query doesn't doesn't recognize the subquery
alias "hours". Is there a way around this problem or does MS Query
have some kind of special syntax for these situations?

My guess is the problem is with '981382'. This number is in single quotes
which means it is being treated as a string instead of a number. I would
thing this is hours which need to be divided by 24 to get days or the single
quotes removed. O r move the single quotes around the whole statement

WHERE 'orders.id = 981382'



" wrote:

Hi
I'm having problems creating queries to MySQL database with MS Query.
For example the following query:

SELECT orders.id AS id,
hours.hourssum AS hourssum
FROM
orders LEFT JOIN
(SELECT job.id AS id, sum(job.hours1) AS hourssum FROM job GROUP BY
job.id) AS hours ON orders.id=hours.id
WHERE orders.id = '981382';

gives me an error: "Unknown column hours.hourssum in 'field list'".
Same query works in MySQL Query Browser just fine. I know that the
query can be done a bit differently so it works in MS Query, but this
query is just an example of the problem I'm having.

The problem is that MS Query doesn't doesn't recognize the subquery
alias "hours". Is there a way around this problem or does MS Query
have some kind of special syntax for these situations?

On 1 joulu, 18:37, Joel wrote:
My guess is the problem is with *'981382'. *This number is in single quotes
which means it is being treated as a string instead of a number. *I would
thing this is hours which need to be divided by 24 to get days or the single
quotes removed. *O r move the single quotes around the whole statement

WHERE 'orders.id = 981382'


Well, that's not really the problem. In this case the id has to be in
single quotes, because some id's are in string format.

On 2 joulu, 08:17, wrote:
On 1 joulu, 18:37, Joel wrote:

My guess is the problem is with *'981382'. *This number is in single quotes
which means it is being treated as a string instead of a number. *I would
thing this is hours which need to be divided by 24 to get days or the single
quotes removed. *O r move the single quotes around the whole statement

WHERE 'orders.id = 981382'

Well, that's not really the problem. In this case the id has to be in
single quotes, because some id's are in string format.

I solved the problem. Following query works fine:

SELECT orders.id AS id,
hours.hourssum AS hourssum
FROM
(orders) LEFT JOIN
(SELECT job.id AS id, sum(job.hours1) AS hourssum FROM job GROUP BY
job.id) AS hours ON orders.id=hours.id
WHERE orders.id = '981382';

So the only thing that is different is brackets around orders-table
right after FROM keyword.
I don't know why it worked fine without brackets in MySQL Query
Browser. Maybe it's because I'm using ODBC to connect to database from
Excel.

Thanks anyway