Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL   how to use calculation in WHERE clause

Posted on 2011-03-17
6
Medium Priority
?
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 24

Accepted Solution

by:
jimyX earned 1000 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 1000 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

704 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