Solved

MySQL   how to use calculation in WHERE clause

Posted on 2011-03-17
6
349 Views
Last Modified: 2012-05-11
i initally tried to do
SELECT     (long_equation_here_that_uses_fields_in_this_row) AS Mileage ...  
      ...   WHERE   Mileage < TblFieldMileage  ...

in order to select only the Rows (records)  where the calculated Mileage was less than the table's field content value for that row.

How can i accomplish this on the fly calculation PER ROW in order to use in a relational expression in the WHERE clause?
0
Comment
Question by:willsherwood
  • 2
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
jimyX earned 250 total points
ID: 35156995
Small adjustment.
You can use it in the where:

SELECT  *  from table1
WHERE TblFieldMileage > (long_equation_here_that_uses_fields_in_this_row)
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35157003
you cannot use the alias in the WHERE, only in the ORDER BY.
so, you have to repeat the expression....

unless you put a subquery:

SELECT *
  FROM ( 
SELECT   TblFieldMileage  ,   (long_equation_here_that_uses_fields_in_this_row) AS Mileage 
    FROM ...
  WHERE ...   
 ) as sub_query
WHER Mileage < TblFieldMileage  ...

Open in new window

0
 

Author Comment

by:willsherwood
ID: 35157063
Thanks Angel, that's even better!   I did not know about subqueries.
Wish i could give you points, sorry for the timing overlap.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35157273
willsherwood,
You can request the moderator attention, and ask for the redistribution of points, as you prefer.
0
 

Author Comment

by:willsherwood
ID: 35173433
i requested attn 3/17  and no response, so i'm trying again now 3/19
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question