Solved

MySQL   how to use calculation in WHERE clause

Posted on 2011-03-17
6
337 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 142

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now