[gk]

Okay, I have a large sql statement and have highlighted the important sections.

I get the error "Unknown column 'name' in 'field list'"

I know it has to deal with this part of the statement

IF(`name` IS NULL or `name`='', 1, 0) AS `nameIsNULL`

But `name` is a field here

IF(`grouping`=1,`categories`.`name_en`,`items`.`name_en`) AS `name`

But that should work, should it not? Since something similar is done with `brand`

As seen in the full sql below.
I'm using MySQL 4.1 any ideas or help would be much appreciated. Thank you. Especially if you can point out a better way to do this query.

SELECT 
 `items`.`id` AS `id`,
 IF(`grouping`=1,CONCAT(`categories`.`name_en`,'_'),`items`.`item_code`) AS `item_code`,
 IF(`grouping`=1,`categories`.`name_en`,`items`.`name_en`) AS `name`,
 `prices`.`quantity` AS `quantity`,
 IF((`prices`.`start`<=NOW() OR `prices`.`start`='0000-00-00' OR `prices`.`start`<=>NULL) AND
(`prices`.`stop`>=NOW() OR `prices`.`stop`='0000-00-00' OR `prices`.`start`<=>NULL),1,0) AS `sale`,
 `prices`.`unit_price` AS `price`,
 `items`.`desc_en` AS `description`,
 `items`.`spec_en` AS `specification`,
 `brands`.`name_en` AS `brand`,
 `prices`.`start` AS `start`,
 `prices`.`stop` AS `end`,
 `categories`.`grouping` AS `grouping`,
 `categories`.`id` AS `cat_id`,
 IF(`name` IS NULL or `name`='', 1, 0) AS `nameIsNULL`,
 IF(`brand` IS NULL or `brand`='', 1, 0) AS `brandIsNULL` FROM `shop_items` AS `items`
 LEFT JOIN `shop_item_prices` AS `prices` ON (`prices`.`item_id`=`items`.`id`)
 LEFT JOIN `shop_brands` AS `brands` ON (`brands`.`id`=`items`.`brand`)
 LEFT JOIN `shop_items_categories` AS `item_cat` ON `item_cat`.`item_id`=`items`.`id`
LEFT JOIN `shop_categories` AS `categories` ON `categories`.`id`=`item_cat`.`category_id`
WHERE 
 (`prices`.`start`<=NOW() OR `prices`.`start`='0000-00-00' OR `prices`.`start`<=>NULL)
 GROUP BY `item_code` ASC,
 `id` ASC
 ORDER BY `nameIsNULL` ASC,
 `name` ASC,
 `brandIsNULL` ASC,
 `brand` ASC
;

[gk]

That was messy... I decided to modify the database a little so the query would be simpler.

Thank you to anyone who looked at this for more then 3 seconds

[Larixk]

you're welcome

[gk]

I guess this should go here... but I am am wondering if this is a bug in mysql or if my syntax is just borked

Say I have a table called "table" it has two fields called "field" and "real"

it's contents are:

 --------------
| field | real |
 --------------
| NULL  | foo  |
| NULL  | bar  |
| 1     | zim  |
| x     | gir  |
 --------------

I want to sort the table with the nulls at the bottom. I made this query

SELECT `field` as `fake`,IF(`fake` IS NULL or `fake`='', 1, 0) AS `IsNULL` FROM `table` ORDER BY `IsNULL`;

Which failes saying "Unknown column 'fake' in 'field list'"

I found if I change the alias to the name of a field existing in the table in this case "real" it works.

SELECT `field` as `real`,IF(`real` IS NULL or `real`='', 1, 0) AS `IsNULL` FROM `table` ORDER BY `IsNULL`;

Is this a bug in mysql or is my syntax wrong?

Of course using an alias in the above query is useless but this is a simplified example.

Any ideas or should I file a bug report?