Adding a field from another table to results

Question

If I have one table with companies in it, and another table with users in it, how do I take a field from the company table and apply it to all the user results?

Users have a company_id field tied to the company's id field.

company table:

id
name

user table:

id
company_id
first_name

---

How do I get a result with:

id   #   company_id   #   first_name   #   company
#####################################################
123  #   20           #   me           #   my company
124  #   20           #   otheruser    #   my company

I'm guessing it could be something like SELECT * FROM usertable, companytable UNION ALL but I'm not sure. Current implementation is looping with php to grab the company for each user, but this doesn't allow SQL "like searching" the company name!

Answer 1

This is another method:

SELECT user.id, user.company_id, user.first_name, company.name 
FROM user, company 
WHERE user.company_id=company.id

Answer 2

You can simply JOIN those tables on the specified column:

SELECT users.id, users.company_id, users.first_name, companies.name
FROM users INNER JOIN companies ON users.company_id = companies.id