Order one table with id's of other table components

[narked]

I read that its not good to repeat the same data in table rows so I am using id's instead of repeating the same word. instead of:

  [B]name[/B]     [B]company[/B] 
      Guy1                 company1
      Guy2                 company1
      Guy3                 company1
      Guy4                 company2

This is the good one:

    [B]name[/B]     [B]company_id[/B] 
      Guy1                     1
      Guy2                     1
      Guy3                     1
      Guy4                     2

and in another table:
company_id company_name
1 company1
2 company2

Ok, the problem is that if I want to order the results by company name how can I do that? because if I use the following code: "SELECT * FROM xxx ORDER BY company_id. the results would be ordered by the company id not the company name!

In the company table the company names are not ordered by name. so if the results are listed by company id, the company names would not be in order.

the big problem is that in one table I have like 5 referenced columns like the example and I need to be able to order the results in any column order.

Thanks for your help!

[Drakla]

I've just tested this with the Northwind database you get with Access (Which I've uploaded to SQL) and this line below does the kind of thing I think you want.

SELECT products.* FROM products
INNER JOIN suppliers ON
products.supplierid=suppliers.supplierid
ORDER BY companyname;

or if you don't like that INNER JOIN tripe,

SELECT products.* FROM products, suppliers WHERE products.supplierid=suppliers.supplierid ORDER BY companyname;'

Just replace products and suppliers with your table names.

Hope it helps ya.