Using data from TWO different tables to create result set?

Databases
1

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?

2

what you want is a join query

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

:slight_smile:

3

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

4

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?

5

yes

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

:slight_smile:

6

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!

7

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

:slight_smile:

8

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!