I have 2 tables

Vendor

 ID  | userid| address | Country
 1   | 10     | NY     | US
 2   | 20     | Mumbai | INDIA

events_todo

 ID  | events_id| vendor| status
 1   | 1        | 10    | Completed
 2   | 2        | 20    | Inprogress

So I want to join these 2 tables and get all vendor table data. I want to join on the basis of if userid of vendor table exists in event_todo table's vendor. I also want to show of column of Count if the status is completed. For example:

 ID  | userid | address | Country | events_id | status     | Count
 1   | 10     | NY     | US       |  1         | Completed |  1
 2   | 20     | Mumbai | INDIA    |  2         | Inprogress|  0

I have applied the following query and I am getting results but not able to get the count of event status

   SELECT `vendors`.`id`, `vendors`.`userid`, `vendors`.`address`,`vendors`.`country` AS `updatedAt`, `vendors`.`userid` IN (SELECT sum(events_todo.status) AS completed FROM events_todo ) AS `completed`, `events_todo`.`id` AS `events_todo.id`, `events_todo`.`events_id` AS `events_todo.events_id`, `events_todo`.`category` `events_todo.vendor`, `events_todo`.`created_by` `events_todo.status` FROM `vendors` AS `vendors` LEFT OUTER JOIN `events_todo` AS `events_todo` ON `vendors`.`userid` = `events_todo`.`vendor` WHERE (`vendors`.`city` LIKE '%%' AND `vendors`.`state` LIKE '%%' AND `vendors`.`country` LIKE '%%') AND events_todo.status IS NOT NULL

I am getting a Count of 0 for Status seems like the sum is not working properly. Please suggest what needs to be done.

CodePudding user response：

You could go with a simple query like this

SELECT v.id, v.user_id, v.address, v.country, e.events_id, e.status, IF(e.status = 'Completed', 1, 0) AS count
FROM vendors AS v
LEFT JOIN events_todo AS e ON e.vendor = v.user_id

Output