Lock Down Lock Down - 5 months ago 20
SQL Question

Mysql Count related records not counting

I have a multiple tables for a library database and I want to count a how many publications has been lent to a certain group.

I have these tables

Patrons table

CREATE TABLE `patrons` (
`id` int(10) UNSIGNED NOT NULL,
`category_id` int(10) UNSIGNED NOT NULL,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




Patron Categories

CREATE TABLE `patron_categories` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`overdue` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




Publications

CREATE TABLE `publication` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL,
`year` year(4) NOT NULL,
`ISBN` varchar(255) NOT NULL,
`type` varchar(24) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




Checkout

CREATE TABLE `checkouts` (
`id` int(10) UNSIGNED NOT NULL,
`publication_id` int(10) UNSIGNED NOT NULL,
`patron_id` int(10) UNSIGNED NOT NULL,
`checkout` date NOT NULL,
`checkin` date NOT NULL,
`actual_checkin` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;





This is my query to list patron categories with their total patrons and their current number of publications their checkout out.



SELECT pc.NAME AS Category,
Count(p.id) AS patrons,
Count(c.id) AS publications
FROM patron_categories AS pc
JOIN patrons AS p
ON pc.id = p.category_id
LEFT JOIN checkouts AS c
ON c.actual_checkin = NULL
LEFT JOIN publication AS pub
ON c.publication_id = pub.id
GROUP BY Category
ORDER BY Category


I don't know what's wrong but it gives the number of publications as 0 while there are many publications lent to those patrons.
Any help please.
The full database with dummy data is located here.

vkp vkp
Answer

The problem with your query is

LEFT JOIN checkouts AS c 
          ON c.actual_checkin = NULL

The =NULL condition always returns null which is neither true nor false. (Use is null or is not null when trying to check for null values.) Hence you would see a 0 count for the publications (count(c.id)) in the result. Add an appropriate join condition for the query to give the expected result.