[thelist] SQL: finding records whose most recent status is...
Judah McAuley
judah at wiredotter.com
Fri Jun 9 10:43:03 CDT 2006
jason.handby wrote:
> Looking at your query above, I'm a bit confused about what the id column
> in customer_order_status_history represents. Is it the id of the
> customer order (which is what you've implied by comparing it to the
> customer_order id in the WHERE clause)? Or is it the id of that
> particular status record (which is what you imply by using MAX())?
Hi Jason, thanks for chiming in. I think you may have missed my first
message in which I laid out the table structure, which is:
customer_order:
id
name
customer_order_status_history:
id
customer_order_id
status
I pretty much always use autoincremented integer id's (well,
technically, numeric 9) as primary keys and then tablename_id as foreign
keys.
>
> SELECT
> customer_order.id, customer_order.name,
> customer_order_status_history.status
> FROM
> customer_order
> INNER JOIN customer_order_status_history
> ON customer_order.id =
> customer_order_status_history.customer_order_id
> WHERE
> customer_order_status_history.id =
> (SELECT MAX(id) FROM customer_order_status_history
> WHERE customer_order_id = customer_order.id)
> AND customer_order_status_history.status = 'Pending'
I don't believe this should work though as you can't refer to the
current row's id (WHERE customer_order_id = customer_order.id) in the
WHERE clause. Unless I'm terribly mistaken. I'll give it a try when I
get into the office later though.
Thanks for the input.
Judah
More information about the thelist
mailing list