Take the 2-minute tour ×

Here are my DB tables:

  - ID, name
  - Order ID, name, etc.
  - Order ID
  - Field ID
  - Value

For example, a Field name may be "First Name," and the OrderField value may be "James."

Using a MySQL query only, how can I efficiently sort Orders based on the OrderFields? To be a little more specific:

Say I want a list of orders sorted by the "First Name" field, and I already have its field ID. How can I write the query to include an ORDER BY statement to sort by the "First Name" OrderField associated with the order?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Your query should be something like:

SELECT Order.*, OrderField.Value
  FROM Order
  LEFT OUTER JOIN OrderField ON Order.`Order ID` = OrderField.`Order ID`
 WHERE OrderField.`Field ID` = :firstNameFieldId
 ORDER BY OrderField.Value

:firstNameFieldId is the id of the "First Name" field.
Outer join is used in case you have orders that do not have "First Name" field populated - they'll still be retrieved in this case. If you're sure this will never happen (or you don't want them selected) replace it with an inner join.

share|improve this answer
Thank you ChssPly! –  James Skidmore Jul 22 '09 at 16:02
Great explanation by the way. –  James Skidmore Jul 22 '09 at 16:03

Your Answer


