SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast afxsguy's Avatar
    Join Date
    May 2005
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you want is a join query
    Code:
    SELECT manufacturers.manufacturername
         , products.modelname
      FROM products
    INNER
      JOIN manufacturers
        ON manufacturers.id = products.manufacturerid
    ORDER
        BY manufacturers.manufacturername
         , products.modelname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast afxsguy's Avatar
    Join Date
    May 2005
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great thank you... I will try this over the weekend!

  4. #4
    SitePoint Enthusiast afxsguy's Avatar
    Join Date
    May 2005
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afxsguy View Post
    Is there I can disclude the "id" column in my "manufacturers" table?
    yes

    don't use the dreaded, evil "select star"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast afxsguy's Avatar
    Join Date
    May 2005
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afxsguy View Post
    ...i wish there was a way you could do this:
    nope

    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast afxsguy's Avatar
    Join Date
    May 2005
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nope that's a common request, but it's not possible
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •