...

View Full Version : How do I use postcount in WHERE ?



needsomehelp
01-02-2012, 01:10 PM
I have this query which works without the


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 ?



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

needsomehelp
01-02-2012, 02:47 PM
postcount is not in a table i have it is what is added to the new table of results. COUNT(*) as postcount

_Aerospace_Eng_
01-02-2012, 07:48 PM
I wonder if you have do something like

COUNT(*) as `l`.`postcount`

Old Pedant
01-02-2012, 10:01 PM
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.

Old Pedant
01-02-2012, 10:08 PM
In this case, you could just change to


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/showthread.php?p=818192#post818192

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

Thus:


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.

Old Pedant
01-02-2012, 10:11 PM
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


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum