CODEWITHSUNDEEP
mysql
user3778390
user3778390

Reputation: 395

MySQL select query from two tables

I have two tables: users and works

I need write select query for count different names from users table where work_status = 1 from works table

enter image description here

The total is: 3 John, 1 Tom

I need get result:
John 2 (2 because one John work_status = 0 ant this not counting)
Tom 1

I have write select that can count different names, just need compared work_status..

SELECT name,COUNT(*) as num FROM users GROUP BY name

My query return:

enter image description here

Upvotes: 0

Views: 77

Answers (4)

Mahesh Patil
Mahesh Patil

Reputation: 1551

SELECT 
  users.`name`,
  COUNT(*) num 
FROM
  users,
  works 
WHERE users.`id` = works.`user_id` 
  AND works.`work_status` = 1 
GROUP BY users.`name` ;

Upvotes: 1

JCalcines
JCalcines

Reputation: 1286

There is a problem in your question. So here you have two solutions.

If there are three different John working on the company, this is your query

SELECT u.name, COUNT(*) as num 
FROM users u INNER JOIN works w ON w.user_id = u.id 
WHERE w.work_status = 1
GROUP BY u.name, u.id

If there are only one John working in the company, your query is this one:

SELECT u.name, COUNT(*) as num 
FROM users u INNER JOIN works w ON w.user_id = u.id 
WHERE w.work_status = 1
GROUP BY u.name

Note: If three John are the same person, you should delete the 2 last and on the works table change user_id = 3 and user_id = 4 for user_id = 1

Upvotes: 2

Arth
Arth

Reputation: 13110

This is a simple JOIN query:

  SELECT u.name, COUNT(*) num
    FROM users u
    JOIN works w
      ON w.user_id = u.id
     AND w.work_status = 1
GROUP BY u.name

Upvotes: 1

LHristov
LHristov

Reputation: 1123

This one should do the job:

SELECT users.name,SUM(works.work_status) as num 
  FROM users,works 
  WHERE users.id=works.id 
  GROUP BY name

Upvotes: 1

Related Questions