CODEWITHSUNDEEP
mysql
mattymattmatt
mattymattmatt

Reputation: 31

Creating a third alias from the remainder of two aliases in MYSQL?

Is it possible to create a third alias from the remainder of two aliases in MYSQL?

Here is my query:

SELECT DISTINCT
workcards.task, 
workcards.program, 
workcards.zone, 
workcards.CYLcomp, 
workcards.CYLint, 
workcards.task, 
workcards.CYLcomp + workcards.CYLint AS nextdue,
workcards.wcnumber,
(SELECT airtimes.total_cycles
       FROM airtimes 
       WHERE airtimes.nnumber='xxxxx'
       ORDER BY airtimes.total_cycles DESC LIMIT 1) AS current_cycle
FROM workcards
WHERE basis LIKE '%CYL%'
AND nnumber='xxxxx'
ORDER BY CASE nextdue
WHEN 0 THEN 1 ELSE -1 END ASC, nextdue ASC

What I want to do is create a third alias which would be the difference of the 'nextdue' minus 'current_cyle' aliases? Something like:

nextdue - current_cycle AS remainder?

Sorry if noob or whatever.

Thanks

Upvotes: 3

Views: 58

Answers (1)

piotrekkr
piotrekkr

Reputation: 3181

You cannot use calculations results aliases to use with another calculations in select part (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html). But you can get what you want by rebuilding your query so it will use joins and grouping:

SELECT DISTINCT 
  w.*, 
  w.CYLcomp + w.CYLint AS nextdue, 
  MAX(a.total_cycles) AS current_cycle
  w.CYLcomp + w.CYLint - MAX(a.total_cycles) AS reminder
FROM workcards w
JOIN airtimes a ON a.nnumber = w.nnumber
WHERE w.basis LIKE '%CYL%' AND w.nnumber='xxxxx'
GROUP BY w.nnumber
ORDER BY CASE nextdue WHEN 0 THEN 1 ELSE -1 END ASC, nextdue ASC

Upvotes: 1

Related Questions