Microsoft Query - subquery alias

[sVaba]

Hi,
I've had this problem for a while and I haven't seen any answers to it yet. Hope you guys know better:

I'm trying to get information to Excel by using an SQL statement that is run in Microsoft Query. Seems that MS Query doesn't follow SQL standards, because the following query gives me this error: "Unknown column hours.hourssum in 'field list'". The same query is excecuted normally in MySQL Query Browser.

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

I know that the query can be done a bit differently, but it's just an example of the situation I have (My real query is much longer and all the field names are in Finnish so I think the situation is more clearer in this way).
The problem is that whenever I use an alias with subqueries and try to return it back to the main query, MS Query doesn't recognize the subquery alias.

Is it even possible in MS Query to use subquery aliases in main query?

 

[xenou]

Maybe try:

SELECT orders.id AS [id],
hours.hourssum AS [hourssum]

field headers in brackets is one of the quirks of MSQuery.

-------------------------------------------------------
Edit: also watch for criteria going into HAVING clauses instead of WHERE clauses, when using aggregate functions...MSQuery botches this at times.

 

[sVaba]

Thanks for the tips.
I forgot to tell that I'm trying to get data from MySQL database. Now the MySQL complaints about brackets around field headers.

Also using keyword HAVING insted of WHERE didn't work. It still complaints about unknown column.

I think you are right about using brackets. Problem is that MySQL doesn't want any extra brackets around field headers (or at least square brackets)

Any other ideas?

 

[sVaba]

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

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

Thanks anyway

 

[xenou]

That's interesting but mysterious! Good tip.

 

[potter_ricky]

Square brackets! Thanks for this xenou, you have no idea how frustrated I've been getting with MS Query losing aliases!