#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    Germany
    Posts
    229
    Rep Power
    13

    Using a calculated select value in where condition


    Hi all,

    I am having some problems with a query.

    It's rather long so I'll stick with the problematic parts.

    I select from different tables and left join them together.

    One value has to be calculated. It's the delay in minutes calculated from unix timestamps and looks like this:

    Code:
    ROUND(TIME_TO_SEC(TIMEDIFF(FROM_UNIXTIME(base.departure_forecast), FROM_UNIXTIME(base.departure)))/60) AS delay
    This works very well.

    I then wanted to use the value "delay" in the where condition.
    However I can't seem to use it there.

    Using this one worked however:
    Code:
    HAVING delay >=10
    The problem I am having now is a bit more complicated as I need to extend my where condition.
    I need the departure time + delay to be before the current time.

    Which would look something like this:
    Code:
    AND (base.departure + (delay * 60)) > 1372658400
    The timestamp is inserted via my php script, but as stated earlier: "delay" is not available in the where condition.

    If I add the condition to the "having" part, the "base.departure" column is not available. So the same problem, but the other way round...

    Is there a way to make it available without an additional query?

    thanks a lot in advance and kind regards

    Sven
    Last edited by winddancer; July 2nd, 2013 at 01:09 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by winddancer
    Is there a way to make it available without an additional query?
    yes

    put the calculation of the delay into the SELECT clause of your query (it's probably already there, right?)

    then put this query as a subquery in the FROM clause of a new query

    in the new query, you can access the calculated column in the WHERE clause

    example:
    Code:
    SELECT foo
         , bar
         , foo/bar AS ratio
      FROM ( SELECT baz * fap AS foo
                 , qux / fap AS bar
               FROM daTable ) AS dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo