#1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    697
    Rep Power
    7

    Cross reference MySQL table for SELECT?


    Howdy. I am going to SELECT customers from a MySQL database, and loop out a table. Everything I need is in a single table EXCEPT Company. Its id IS in this table, but I need the name. Is there a way to do something similar as:

    SELECT `company`,`member`,`phone` FROM `table`

    and instead of getting the 138 value in this table for `company`, have it fetch that from a different table, but use the 138 in the member table to reference it in the 2nd table? Thank for any input.
  2. #2
  3. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Code:
    SELECT companies.name AS company_name
         , members.member
         , members.phone 
      FROM members
    INNER
      JOIN companies
        ON companies.id = members.company
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    697
    Rep Power
    7
    INNER JOIN, excellent. Thank you. Trying to use that, a column has a space in it, but using tablename.column won't allow me to place backtick's around it. What shall I do?

    Code:
    SELECT `customers.Customer Name`,`rolodex.FLName`,`rolodex.Position`,`rolodex.Number`,`rolodex.Type`,`rolodex.E-mail`,`rolodex.Note` FROM `rolodex` INNER JOIN customers ON rolodex.CustomerID=customers.ID ORDER BY `customers.Customer Name`
    Last edited by Triple_Nothing; February 6th, 2013 at 02:17 PM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    697
    Rep Power
    7
    Ah, backticks only go around field names, not tables. Thanks all!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,477
    Rep Power
    1752
    No, they go round (when you need to use them) both table name and column name, but each is a separate entity, thus: `Table name`.`Column name`
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    697
    Rep Power
    7
    Ah. Thanks for the clarity.

IMN logo majestic logo threadwatch logo seochat tools logo