Re: [PHP-DB] Re: query problem..
For those that are curious, here's the working query:
SELECT options. * , count( option_items.optionID ) AS n
FROM options
LEFT JOIN option_links
USING ( optionID )
LEFT JOIN option_items ON options.optionID = option_items.optionID
WHERE option_links.productID = '$productID'
GROUP BY options.optionID
HAVING n > 0
-Micah
On Wednesday 14 July 2004 12:07, Micah Stevens wrote:
> It is a column.. ;)
>
> And the docs say you can refer to an aliased aggregate column in the where
> clause:
>
> http://dev.mysql.com/doc/mysql/en/SELECT.html
>
> I'll take this to the mysql list.. Thanks for your help..
>
> -Micah
>
> On Wednesday 14 July 2004 12:01, Torsten Roehr wrote:
> > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> >
> > > Thank you Torsten ,
> > >
> > > They're backticks, and I get the same error after removing them.
> > >
> > > Any other ideas?
> >
> > Then I guess you can't use num in your WHERE clause - MySQL thinks it's a
> > column.
> >
> > Torsten
> >
> > > -Micah
> > >
> > > On Wednesday 14 July 2004 11:48, Torsten Roehr wrote:
> > > > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > > > news:[EMAIL PROTECTED]
> > > >
> > > > > Hi,
> > > > >
> > > > > I'm getting an unknown colum `num` in where clause error with this
> >
> > query:
> > > > > $options = mysql_query("select options.*,
> > > > > count(option_items.optionID) as `num`
> > > > > from options
> > > > > left join option_links using (optionID)
> > > > > left join option_items on options.optionID = option_items.optionID
> > > > > where option_links.productID = '$productID'
> > > > > and `num` > 0");
> > > > >
> > > > > But I'm defining it with the agregate function in the second line..
> >
> > Any
> >
> > > > idea
> > > >
> > > > > why it's popping the error?
> > > > >
> > > > > -Micah
> > > >
> > > > Remove the quotes around num and try again.
> > > >
> > > > Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: query problem..
Sorry, I'm wrong..
WHERE cannot refer to aggregate columns.. you must use the HAVING clause.
-Micah
On Wednesday 14 July 2004 12:07, Micah Stevens wrote:
> It is a column.. ;)
>
> And the docs say you can refer to an aliased aggregate column in the where
> clause:
>
> http://dev.mysql.com/doc/mysql/en/SELECT.html
>
> I'll take this to the mysql list.. Thanks for your help..
>
> -Micah
>
> On Wednesday 14 July 2004 12:01, Torsten Roehr wrote:
> > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> >
> > > Thank you Torsten ,
> > >
> > > They're backticks, and I get the same error after removing them.
> > >
> > > Any other ideas?
> >
> > Then I guess you can't use num in your WHERE clause - MySQL thinks it's a
> > column.
> >
> > Torsten
> >
> > > -Micah
> > >
> > > On Wednesday 14 July 2004 11:48, Torsten Roehr wrote:
> > > > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > > > news:[EMAIL PROTECTED]
> > > >
> > > > > Hi,
> > > > >
> > > > > I'm getting an unknown colum `num` in where clause error with this
> >
> > query:
> > > > > $options = mysql_query("select options.*,
> > > > > count(option_items.optionID) as `num`
> > > > > from options
> > > > > left join option_links using (optionID)
> > > > > left join option_items on options.optionID = option_items.optionID
> > > > > where option_links.productID = '$productID'
> > > > > and `num` > 0");
> > > > >
> > > > > But I'm defining it with the agregate function in the second line..
> >
> > Any
> >
> > > > idea
> > > >
> > > > > why it's popping the error?
> > > > >
> > > > > -Micah
> > > >
> > > > Remove the quotes around num and try again.
> > > >
> > > > Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: query problem..
It is a column.. ;)
And the docs say you can refer to an aliased aggregate column in the where
clause:
http://dev.mysql.com/doc/mysql/en/SELECT.html
I'll take this to the mysql list.. Thanks for your help..
-Micah
On Wednesday 14 July 2004 12:01, Torsten Roehr wrote:
> "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
> > Thank you Torsten ,
> >
> > They're backticks, and I get the same error after removing them.
> >
> > Any other ideas?
>
> Then I guess you can't use num in your WHERE clause - MySQL thinks it's a
> column.
>
> Torsten
>
> > -Micah
> >
> > On Wednesday 14 July 2004 11:48, Torsten Roehr wrote:
> > > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > > news:[EMAIL PROTECTED]
> > >
> > > > Hi,
> > > >
> > > > I'm getting an unknown colum `num` in where clause error with this
>
> query:
> > > > $options = mysql_query("select options.*,
> > > > count(option_items.optionID) as `num`
> > > > from options
> > > > left join option_links using (optionID)
> > > > left join option_items on options.optionID = option_items.optionID
> > > > where option_links.productID = '$productID'
> > > > and `num` > 0");
> > > >
> > > > But I'm defining it with the agregate function in the second line..
>
> Any
>
> > > idea
> > >
> > > > why it's popping the error?
> > > >
> > > > -Micah
> > >
> > > Remove the quotes around num and try again.
> > >
> > > Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: query problem..
"Micah Stevens" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Thank you Torsten ,
>
> They're backticks, and I get the same error after removing them.
>
> Any other ideas?
Then I guess you can't use num in your WHERE clause - MySQL thinks it's a
column.
Torsten
>
>
> -Micah
>
>
>
> On Wednesday 14 July 2004 11:48, Torsten Roehr wrote:
> > "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> >
> > > Hi,
> > >
> > > I'm getting an unknown colum `num` in where clause error with this
query:
> > >
> > > $options = mysql_query("select options.*,
> > > count(option_items.optionID) as `num`
> > > from options
> > > left join option_links using (optionID)
> > > left join option_items on options.optionID = option_items.optionID
> > > where option_links.productID = '$productID'
> > > and `num` > 0");
> > >
> > > But I'm defining it with the agregate function in the second line..
Any
> >
> > idea
> >
> > > why it's popping the error?
> > >
> > > -Micah
> >
> > Remove the quotes around num and try again.
> >
> > Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: query problem..
Thank you Torsten ,
They're backticks, and I get the same error after removing them.
Any other ideas?
-Micah
On Wednesday 14 July 2004 11:48, Torsten Roehr wrote:
> "Micah Stevens" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
> > Hi,
> >
> > I'm getting an unknown colum `num` in where clause error with this query:
> >
> > $options = mysql_query("select options.*,
> > count(option_items.optionID) as `num`
> > from options
> > left join option_links using (optionID)
> > left join option_items on options.optionID = option_items.optionID
> > where option_links.productID = '$productID'
> > and `num` > 0");
> >
> > But I'm defining it with the agregate function in the second line.. Any
>
> idea
>
> > why it's popping the error?
> >
> > -Micah
>
> Remove the quotes around num and try again.
>
> Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
