SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query Question

  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Question

    Why can't I access prostatus in WHERE clause, whilst I can access it ORDER BY clause.

    Eg:
    SELECT ...,IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) AS prostatus
    FROM ....
    WHERE
    prostatus = 1 AND p2.pr_type_id = 1 ....
    <- Doesn't work (Unknown column 'prostatus' in 'where clause'). Yet, the following DOES work:

    SELECT ...,IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) AS prostatus
    FROM ....
    WHERE
    p2.pr_type_id = 1 ....
    ORDER BY prostatus DESC

    Assuming everything else is the same in the query.

    Thanks

  2. #2
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No. you can't use that alias in the where clause. You may try to query like:

    WHERE
    (IF((p2.pr_status = 0 OR psd2.link_status = 0),0,1) = 1) AND p2.pr_type_id = 1

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I worked out I could also do HAVING prostatus = 1 after the WHERE clause.
    Do you see any complications with doing it this way?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •