Excel 2003, Microsoft Query - reference to subquery alias
 

Hi, I have problem with MS Query in Excel 2003.
I'm trying to get information from MySQL database to Excel with an
SQL-clause that contains a few subqueries. For example, the following
SQL-clause gives me an error saying: "Unknown column hours.hourssum in 'field
list'"

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 = '123';

I know that the query above can be done a bit differently, but it's just an
example of the problem I have. The problem is that MS Query can't see the
table alias "hours" and gives me a complaint that it doesn't exist. Same
SQL-clause works fine in MySQL Query Browser.

Is there a way around this, maybe a different syntax in SQL-caluse or
something else?

Excel 2003, Microsoft Query - reference to subquery alias
 

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 = '123';

Only thing that is different are the brackets around orders-table, right
after FROM-keyword.

Thanks anyway


"Finnman" wrote:

Hi, I have problem with MS Query in Excel 2003.
I'm trying to get information from MySQL database to Excel with an
SQL-clause that contains a few subqueries. For example, the following
SQL-clause gives me an error saying: "Unknown column hours.hourssum in 'field
list'"

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 = '123';

I know that the query above can be done a bit differently, but it's just an
example of the problem I have. The problem is that MS Query can't see the
table alias "hours" and gives me a complaint that it doesn't exist. Same
SQL-clause works fine in MySQL Query Browser.

Is there a way around this, maybe a different syntax in SQL-caluse or
something else?