Using data from TWO different tables to create result set?

I am wondering if any MySQL experts can help me. Here’s my situation. I have two tables:

  1. TABLE: “manufacturers”

    • COLUMN “id”
    • COLUMN “manufacturername”
    • COLUMN “websiteurl”
    • …and several other columnd which do not matter in this example.
  2. TABLE: “products”

    • COLUMN “id”
    • COLUMN “manufacturerid” (this column contains an INT referencing to one of the rows in the manufacturers table above)
    • COLUMN “modelname”
    • …and several other columnd which do not matter in this example.

When I query the product table, all of the fields I need are raw product data, which makes ORDER BY very easy. However, if I want to ORDER the product results by manufacturer alphabetically, it only has key ID’s (integers that reference to the manufacturer table) to order by. Is there a way to subsitute or combine the manufacturer name (string) into my SELECT * FROM products ORDER BY manufacturer-name-from-other-table-somehow? so that I can ORDER BY manufacturers alphabetically?

what you want is a join query

SELECT manufacturers.manufacturername
     , products.modelname
  FROM products
  JOIN manufacturers
    ON = products.manufacturerid
    BY manufacturers.manufacturername
     , products.modelname


Great thank you… I will try this over the weekend!

Okay I tried INNER JOIN and it is working great, however since both tables have an “id” column, it starts acting funny when I need to use an “id” column value in my PHP. I am guessing this is because the joined table contains two “id” columns. Is there I can disclude the “id” column in my “manufacturers” table?


don’t use the dreaded, evil “select star”


so does that mean you always select each column manually as opposed to using the select star… even in large schemas with many columns? …i wish there was a way you could do this:

SELECT * EXCEPT column_to_disclude FROM tablename …etc

anyway, i should have known how to do this, but thank you for pointing it out. i guess what you are saying is that it’s bad practice to use the select star? once again thanks for all the help!

nope :slight_smile:

that’s a common request, but it’s not possible

really, the best thing is to list all the columns you want, and none of the ones you don’t want


oh okay thanks! here’s hoping that mysql will add this kind of thing in the future. i could see it getting frustrating if i change my table schema (add or edit column names)… i might forget to update all the queries throughout the web application to relect these schema changes. the select star takes care of this. i am however beginning to see how using the select star can create problems too!

anyway, you’ve been very helpful. thank you!