Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    478
    Thanks
    7
    Thanked 3 Times in 3 Posts

    How do I use postcount in WHERE ?

    I have this query which works without the
    Code:
    AND `postcount` > 10
    But with it I can not get any results as mysql states that the field `postcount` does not exist

    How do I use postcount in the WHERE part of the query ?

    Code:
    SELECT `l`.`linklocation`, `l`.`author`, `a`.`account_type`, `a`.`username`, COUNT(*) as postcount
                                 FROM `links` as l
                                 LEFT JOIN `accounts` as a ON a.id=l.author
                                 WHERE `a`.`account_type` = '' AND `postcount` > 10 AND `live` = '1' AND `author` != '3' AND `author` != '1' GROUP BY `author` ORDER BY postcount

  • #2
    Regular Coder
    Join Date
    Oct 2009
    Posts
    478
    Thanks
    7
    Thanked 3 Times in 3 Posts
    postcount is not in a table i have it is what is added to the new table of results. COUNT(*) as postcount

  • #3
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    I wonder if you have do something like
    Code:
    COUNT(*) as `l`.`postcount`
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    No, you cannot give a table prefix to an aliased field name.

    COUNT(*) AS postcount is correct.

    But...

    You can *NOT* use an aliased field name in a WHERE or GROUP BY clause.

    That's because the value of the aliased field name is *NOT KNOWN* until the SELECT portion of the query (which is influenced by the WHERE and GROUP BY clauses) is complete.

    You *can* use an aliased field name in the ORDER BY and HAVING clauses, because those clauses are not execute until after the SELECT portion of the query has completed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    In this case, you could just change to
    Code:
    SELECT l.linklocation, l.author, a.account_type, a.username, COUNT(*) as postcount
    FROM links as l
    LEFT JOIN accounts as a ON a.id=l.author
    WHERE a.account_type = '' AND live = 1 AND l.author != 3 AND l.author != 1
    GROUP BY l.author 
    HAVING postcount > 10 
    ORDER BY postcount
    BUT...

    But your LEFT JOIN there is *NOT* going to be treated as a LEFT JOIN.

    And that's because you used the DEPENDENT table from the left join (that is, the table on the right side of the left join) in your WHERE clause. Doing so effectively converts the LEFT join into an INNER join.

    See here:
    http://www.codingforums.com/showthre...192#post818192

    If you really *need* a LEFT JOIN, then you must move all conditions on your accounts table to the ON clause instead.

    Thus:
    Code:
    SELECT l.linklocation, l.author, a.account_type, a.username, COUNT(*) as postcount
    FROM links as l
    LEFT JOIN accounts as a ON a.id=l.author AND a.account_type = '' 
    WHERE live = 1 AND author != 3 AND author != 1
    GROUP BY author 
    HAVING postcount > 10 
    ORDER BY postcount
    (If the live field is also part of the accounts table then it must be moved to the ON as well.)

    Oh...and I zapped your apostrophes around 1 and 3 and 1. It's technically an error to use apostrophes around values that are to be compared to INT fields. MySQL is sloppy and allows it, converting '1' to 1, '3' to 3, etc., for you. But it's a minor performance hit and is, as I said, syntactically an error.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Whoops...you are also making a big mistake by doing GROUP BY l.author only.

    Most DBs wouldn't even allow that. They would insist on
    Code:
    GROUP BY l.linklocation, l.author, a.account_type, a.username
    That is, any non-aggregate field must be in the GROUP BY.

    MySQL allows it, but then you *CAN NOT PREDICT* what values MySQL will choose for the fields that are not part of the GROUP BY.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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