CODEWITHSUNDEEP
mysql
user3074921
user3074921

Reputation:

How to show null fields with mysql query when no value available?

I've two table "Users" and "Recipient" . Now I want to show all offices and total recipient on this office. But My query returns which offices have count number. But I need to show all office and count numbers.

like :

id          office_name           count
========================================
1             Dhaka                0
2             Chittagong           2

current query: "

SELECT users.id as office_id, users.office_name , count(recipient.id) as total_count 
FROM users 
right outer JOIN recipient on recipient.office_id = users.id
WHERE ( users.del_status = 0 and users.type='agency')
order by users.id

"

Users Table: enter image description here

Recipient Table: enter image description here

Structure:

CREATE TABLE `recipient` (
  `id` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `age` int(10) NOT NULL,
  `reg_no` varchar(10) NOT NULL,
  `address` varchar(255) NOT NULL,
  `disability_type` int(10) NOT NULL,
  `education` varchar(255) NOT NULL,
  `office_id` int(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL,
  `create_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;      

 CREATE TABLE `users` (
  `id` tinyint(4) NOT NULL,
  `office_name` varchar(255) DEFAULT NULL,
  `district_id` int(10) DEFAULT NULL,
  `upazilla_id` int(10) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `type` varchar(10) NOT NULL,
  `del_status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Upvotes: 0

Views: 31

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can use LEFT JOIN to get all the offices even if they don't have entries in recipient table, e.g.:

SELECT u.id, u.office_name, COUNT(r.id) AS `count`
FROM users u LEFT JOIN recipient r ON u.id = r.office_id
GROUP BY u.id, u.office_name;

Upvotes: 1

Related Questions