Order by in mysql using second table

Question

Hi,

I have two tables, one is a list os stores and attributes, the second is a list of allocationsa based on these attributes. The attribute table (stores_metadata)

| key | store_key | field | value
| 1   | 1         | size  | Large
| 2   | 1         | dist  | Midlands
| 3   | 2         | size  | Medium
| 4   | 3         | dist  | South

The allocation table (allocation)

| key | ticket_key | field | value | count
| 1   | 1          | size  | Large | 10
| 2   | 1          | size  | Medium| 5

I've managed to get the allocations working using the code:

SELECT store_key, quantity FROM 
allocation

INNER JOIN store_metadata
  ON allocation.`field` = store_metadata.`field`
    AND allocation.`value` = store_metadata.`value`

This returns a list of the stores and how many items they should recieve, what I now need to do it order the stores by the distribution attribute.

Any help would be greatly appreciated.

Answer 1

The question isn't asked very well.

To perform ordering by any column in your result set add ORDER BY [column] to the end of the query. E.g.

SELECT store_key, quantity FROM 
allocation

INNER JOIN store_metadata
  ON allocation.`field` = store_metadata.`field`
    AND allocation.`value` = store_metadata.`value`
    ORDER BY allocation.`field`;