union and subquery

[kpzani]

Hi all

I have the following nasty query:

SELECT compName, SUM(Value) AS Value FROM (
(SELECT compName, SUM(cCost) AS Value FROM courses
LEFT JOIN contacts ON courses.cClient=contacts.conID LEFT JOIN companies ON
contacts.compID=companies.compID
WHERE cDate BETWEEN '2004-09-01' AND '2005-09-01' GROUP BY compName
) UNION (
SELECT compName, SUM(cCost) AS Value FROM consultancy
LEFT JOIN contacts ON consultancy.cClient=contacts.conID
LEFT JOIN companies ON contacts.compID=companies.compID
WHERE cStartDate BETWEEN '2004-09-01' AND '2005-09-01'
GROUP BY compName
)
) AS temp GROUP BY compName ORDER BY Value DESC LIMIT 5

when i run this on 4.1.14 it works fine. but my isp uses 4.1.8 and this generates an error - every derived table must have its own alias.

any ideas.

cheers

Kris

[Sxooter]

Sure, give each derived table an alias. (and, in the future, put your sql query in a set of code tags and indent it for easy reading)

select x,y,x from ((
        select x,y,z from table join othertable ...
    ) AS TABLE1 UNION (  -- THIS IS A TABLE ALIAS
        select x,y,z from table join othertable ...
        group by somefieldoranother
    ) AS TABLE2 -- THIS IS A TABLE ALIAS
) AS temp group by ....

[kpzani]

sorry about the indention. i should have also specified mysql.
i'd already tried the code you gave and it doesn't work. it generates a syntax error near
") AS whatever UNION ("

apparently this is a bug in the version of mysql my ISP is using,

[Sxooter]

Ahhhh. Well, then you need to inform your ISP that they are running KNOWN broken software, together with the release notes for the next version of MySQL that fixed this etc... Might help if there were any security updates. People will sit on their fat arses when they think all you want is a bug fix, but mention a security fix, even an obscure and relatively non-dangerous one, and suddenly they perk right up.

Either that or find another ISP.