Order by a field from another table

[vigour]

I have a mysql table (album) that looks like this:

counter
type_id
name

I want to order the table like this:

SELECT * FROM album ORDER BY type_id

The problem is that type_id is a integer. I do not want to order by the integer. I want to order by type from another table. I have another table that is called type that have two fields like this:

type_id
type

1
Prince

2
Elton John

3
George Michael

Can someone please point me in the right direction.

[cretaceous]

SELECT * FROM album LEFT JOIN type ON type.type_id=album.type_id ORDER BY type.type

that should do it - you could do the 'order' just as 'order by type' but this is clearer

make life easier though and don't name a column in the db with the same name as the table !

[Saviola]

If type_id isn't require you can use INNER JOIN or add WHERE clause in LEFT JOIN to fetch only these row which have type.
It's a bad practice to give a name on COLUMN same like TABLE which it's in.
Also you have to be careful for SQL reservet word when you give a name on your columns(etc. 'order','type' ... )