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.

Ignacio Vazquez-Abrams · Accepted Answer · 2010-07-16 02:21:23Z

10

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.

answered Jul 16, 2010 at 2:21

Ignacio Vazquez-Abrams

803k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trez Over a year ago

Thanks anyway.. but i have found my own sql. SELECT u.name AS Username, (SELECT c.name FROM Company as c WHERE c.id=u.company_id) as Company_Name FROM user as u WHERE u.status = 'active'

dmkc Over a year ago

I'd stick to Ignacio's answer: he's using a join which is far more efficient than your double-query solution.

Collectives™ on Stack Overflow

MySQL: Get data from other table matching the ids

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related