Not signed in (Sign In)
Welcome Guest!
Want to take part in these discussions? If you have an account, sign in now.
If you don't have an account, apply for one now.

Database Design: SQL: Can we use result columns in the WHERE clause?

Bottom of Page

1 to 2 of 2

  1.  
    • CommentAuthorsuthern
    • CommentTimeJan 10th 2008
     

    If the subject is confusing, notice the last few lines of the code below:

    SELECT spots.id AS spotId, ( SELECT ( physicals.unit_qty * units.qty ) AS last_real_qty FROM physicals LEFT OUTER JOIN units ON physicals.unit_id = units.id WHERE spots.id = physicals.spot_id ORDER BY physicals.created_at DESC LIMIT 1 ) AS last_physical, ( spots.unit_min_qty * units.qty ) AS min_stock FROM spots LEFT OUTER JOIN units ON spots.unit_id = units.id WHERE last_physical <= min_stock

    If I run it without the 'WHERE' clause, it comes out fine, with 'last_physical' and 'min_stock' columns.

    If I run it with the 'WHERE' clause as above, MySQL complains about there not being any 'last_physical or 'min_stock' columns.

    Is there something simple I'm overlooking? Thanks! -Suthern

    • CommentAuthorKaste
    • CommentTimeJan 10th 2008
     

    no. WHERE matches against the 'from' and joined tables. try HAVING.

1 to 2 of 2

  1.  
Add your comments
    Username Password
  • Format comments as
 
Back to Discussions Top of Page