MySQL Forums
Forum List  »  InnoDB

Subquries and Alias Help
Posted by: Josh Stuart
Date: April 17, 2008 11:55PM

Hey everyone i'm hoping you could help me with this query. Ill explain what im trying to do first.

I have a webstore that has stages of processing eg('waiting for payment', 'shipped' etc) in a table called `status`. So there is a set up like:

orders <- orderstatus -> status

therefore the orderstatus can have multiple values for a single order.

However in the CMS i only want to display the last status entry because it would be the most recent and therefore the current state of the order. So I came up with a query but for some reason it doesn't recognise the alias in the where clause even though it recognises it in the order by.


SELECT o.id AS 'ID', o.the_date, c.fname AS 'First Name', c.lname AS 'Last Name', o.payment_type AS 'Payment Type', o.id AS 'oid',
(SELECT s.name
FROM `status` s
WHERE s.id =
(SELECT MAX( os.fk_status ) AS 'maxstatus'
FROM orderstatus os
WHERE os.fk_orders = `oid`
GROUP BY os.fk_orders)
) AS 'Order Status'
FROM orders o, customer c
WHERE c.id = o.fk_customer
AND `Order Status` IS NOT NULL
ORDER BY `Order Status` DESC
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'Order Status' in 'where clause'

The query works fine when i remove "AND `Order Status` IS NOT NULL" but returns NULL values in the field where the max status isn't found.

What am I doing wrong. Also is there maybe a better way of going about this? I'm sure there is because it shouldn't be looking through all the rows and not returning a result hence leaving the `Order Status` field full of NULLs.

any help would be appreciated!

Options: ReplyQuote


Subject
Views
Written By
Posted
Subquries and Alias Help
3300
April 17, 2008 11:55PM
1915
April 18, 2008 09:29AM
1845
April 18, 2008 06:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.