MySQL: Get data from other table matching the ids.

Question

I have table named 'company' and 'user'. The user table contains the id of company. Like for example:

A. User table

user_id | name | company_id |status
1       | john |    1       | active

B. Company table

company_id | name | status
1          | ABC  | active

How to get the name of the company by their id in single sql query. such;

$query = "SELECT name as Username, company_id as Company_Name From `user` where status='active'";

That will give the result of:

Username | Company_Name
  john   | ABC

Any help or ideas on how to do this... Thanks in advance.

Answer 1

SELECT u.name AS Username, c.name AS Company_Name
FROM User AS u
INNER JOIN Company AS c
  ON u.company_id = c.company_id
WHERE u.status = 'active'
  AND c.status = 'active'

Feel free to remove either or both of the expressions in the WHERE clause if they don't make sense.