kailee
asked on
mysql query to show non-payments for specific month(s)
I have a students table and a payments table.
they can be joined by sid.
I need to see sids that have no entry for april. I tried this query, but it shows two students that have an entry for april.
oddly, other students have also paid for april, but they don't show up in the list.
any help would be greatly appreciated.
they can be joined by sid.
I need to see sids that have no entry for april. I tried this query, but it shows two students that have an entry for april.
SELECT students.sid, students.name, students.last_name FROM students LEFT JOIN payments ON students.sid = payments.sid WHERE students.Active = 'Yes' AND payments.forMonth != "Apr" GROUP BY payments.forMonthoddly, other students have also paid for april, but they don't show up in the list.
any help would be greatly appreciated.
Ray Paseur
Please post the CREATE TABLE statements, thanks. A few rows of test data would be helpful, too. Then we can give you a tested and working solution!
ASKER
-- PHP Version: 5.1.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `school`
--
-- --------------------------------------------------------
--
-- Table structure for table `payments`
--
CREATE TABLE `payments` (
`pid` tinyint(4) NOT NULL auto_increment,
`sid` tinyint(4) NOT NULL,
`eid` tinyint(4) NOT NULL,
`amount` int(10) NOT NULL,
`forMonth` enum('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') NOT NULL,
`date` date NOT NULL,
`accrued` int(11) NOT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=66 ;
--
-- Dumping data for table `payments`
--
INSERT INTO `payments` VALUES(1, 15, 0, 8000, 'Apr', '2015-04-11', 0);
INSERT INTO `payments` VALUES(2, 14, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(3, 15, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(4, 16, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(5, 17, 0, 8000, 'Apr', '2015-03-30', 0);
INSERT INTO `payments` VALUES(6, 18, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(7, 19, 0, 8000, 'Apr', '2015-03-30', 0);
INSERT INTO `payments` VALUES(8, 20, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(9, 21, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(10, 22, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(11, 23, 0, 8000, 'Apr', '2015-04-08', 0);
INSERT INTO `payments` VALUES(12, 24, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(13, 25, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(14, 26, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(15, 27, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(16, 28, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(17, 29, 0, 8000, 'Apr', '2015-04-22', 0);
INSERT INTO `payments` VALUES(18, 30, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(19, 31, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(20, 32, 0, 7000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(21, 33, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(22, 34, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(23, 35, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(24, 36, 0, 8000, 'Apr', '0000-00-00', 0);
INSERT INTO `payments` VALUES(25, 37, 0, 8000, 'Apr', '2015-04-18', 0);
INSERT INTO `payments` VALUES(26, 38, 0, 7000, 'Apr', '2015-04-23', 0);
INSERT INTO `payments` VALUES(27, 39, 0, 7000, 'Apr', '2015-04-23', 0);
INSERT INTO `payments` VALUES(28, 40, 0, 8000, 'May', '2015-05-20', 0);
INSERT INTO `payments` VALUES(29, 41, 0, 8000, 'Apr', '2015-04-06', 0);
INSERT INTO `payments` VALUES(30, 42, 0, 8000, 'Apr', '2015-04-25', 0);
INSERT INTO `payments` VALUES(31, 29, 0, 8000, 'Mar', '2015-03-26', 0);
INSERT INTO `payments` VALUES(32, 38, 0, 7000, 'Mar', '2015-04-23', 0);
INSERT INTO `payments` VALUES(33, 39, 0, 7000, 'Mar', '2015-04-23', 0);
INSERT INTO `payments` VALUES(34, 17, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(35, 41, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(50, 43, 0, 8000, 'May', '2015-05-16', 0);
INSERT INTO `payments` VALUES(37, 39, 0, 7000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(38, 38, 0, 7000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(39, 31, 0, 7000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(40, 32, 0, 7000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(41, 35, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(42, 34, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(43, 36, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(44, 42, 0, 8000, 'May', '2015-05-12', 0);
INSERT INTO `payments` VALUES(45, 23, 0, 8000, 'May', '2015-05-13', 0);
INSERT INTO `payments` VALUES(46, 34, 0, 8000, 'May', '2015-05-13', 0);
INSERT INTO `payments` VALUES(47, 34, 0, 8000, 'May', '2015-05-13', 0);
INSERT INTO `payments` VALUES(48, 37, 0, 8000, 'May', '2015-05-17', 0);
INSERT INTO `payments` VALUES(49, 20, 0, 6000, 'May', '2015-05-17', 0);
INSERT INTO `payments` VALUES(51, 44, 0, 6000, 'May', '2015-05-06', 0);
INSERT INTO `payments` VALUES(52, 19, 0, 8000, 'May', '2015-05-18', 0);
INSERT INTO `payments` VALUES(53, 18, 0, 8000, 'May', '2015-05-19', 0);
INSERT INTO `payments` VALUES(54, 16, 0, 8000, 'May', '2015-05-20', 0);
INSERT INTO `payments` VALUES(55, 27, 0, 8000, 'May', '2015-05-20', 0);
INSERT INTO `payments` VALUES(56, 30, 0, 8000, 'May', '2015-05-20', 0);
INSERT INTO `payments` VALUES(57, 39, 0, 7000, 'May', '2015-05-21', 0);
INSERT INTO `payments` VALUES(58, 33, 0, 8000, 'May', '2015-05-21', 0);
INSERT INTO `payments` VALUES(59, 22, 0, 7000, 'May', '2015-05-21', 0);
INSERT INTO `payments` VALUES(60, 28, 0, 7000, 'May', '2015-05-21', 0);
INSERT INTO `payments` VALUES(61, 29, 0, 8000, 'May', '2015-06-02', 0);
INSERT INTO `payments` VALUES(62, 40, 0, 8000, 'Apr', '2015-06-13', 0);CREATE TABLE `students` (
`sid` int(10) NOT NULL auto_increment COMMENT 'student ID',
`cid` tinyint(4) NOT NULL COMMENT 'Class ID',
`bid` tinyint(4) NOT NULL COMMENT 'Book ID',
`hid` tinyint(4) NOT NULL COMMENT 'Homework ID',
`Active` varchar(5) NOT NULL default 'Yes',
`name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`Grade` varchar(128) NOT NULL,
`Parent` varchar(100) NOT NULL,
`Emergency` varchar(256) NOT NULL,
`home` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`phone` varchar(20) NOT NULL,
`Cell` varchar(20) NOT NULL,
`age` varchar(3) NOT NULL,
`bday` date NOT NULL,
`Mender` enum('male','female') NOT NULL,
`hobby` varchar(100) NOT NULL,
`music` varchar(100) NOT NULL,
`level` int(10) NOT NULL,
`lid` int(10) NOT NULL COMMENT 'Level ID',
`stypes` varchar(30) NOT NULL,
`tuition` int(10) NOT NULL,
`day` enum('Mon','Tue','Wed','Thu','Fri','Sat','Sun') NOT NULL,
`time` time NOT NULL,
`summary` varchar(255) NOT NULL,
`gender` varchar(25) NOT NULL,
UNIQUE KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=45 ;
--
-- Dumping data for table `students`
--
INSERT INTO `students` VALUES(14, 0, 0, 0, 'No', 'Yuya', 'Takahashi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(15, 0, 0, 0, 'No', 'Kaito', 'Rikiishi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(16, 0, 0, 0, 'No', 'Hana', 'Usami', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(17, 0, 0, 0, 'Yes', 'Wakana', 'Tsubaki', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(18, 0, 0, 0, 'Yes', 'Chihiro', 'Iwasawa', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(19, 0, 0, 0, 'Yes', 'Yuuri', 'Takeda', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(20, 0, 0, 0, 'Yes', 'Yota', 'Kikuchi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 6000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(21, 0, 0, 0, 'Yes', 'Haruka', 'Matsumaru', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(22, 0, 0, 0, 'Yes', 'Yua', 'Yamashita', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(23, 0, 0, 0, 'Yes', 'Niina', 'Suzuki', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(24, 0, 0, 0, 'No', 'Sara', 'Ashida', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(25, 0, 0, 0, 'Yes', 'Manato', 'Meno', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(26, 0, 0, 0, 'Yes', 'Kokona', 'Meno', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(27, 0, 0, 0, 'Yes', 'Maruko', 'Kawaguchi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(28, 0, 0, 0, 'Yes', 'Daiki', 'Yamashita', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(29, 0, 0, 0, 'Yes', 'Akamichi', 'Tosaki', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(30, 0, 0, 0, 'Yes', 'Ren', 'Nogawa', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(31, 0, 0, 0, 'Yes', 'Misao', 'Takahashi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(32, 0, 0, 0, 'Yes', 'Koto', 'Takahashi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(33, 0, 0, 0, 'Yes', 'Mizuki', 'Amemiya', '', '', '', '', 'sa.wa.ka@ezweb.ne.jp', '090 1265 8384', '0', '10', '0000-00-00', '', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(34, 0, 0, 0, 'Yes', 'Ikumi', 'Chiba', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(35, 0, 0, 0, 'Yes', 'Takuma', 'Izumi', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(36, 0, 0, 0, 'Yes', 'Yuuki', 'Yoshikawa', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(37, 0, 0, 0, 'Yes', 'Nau', 'Yamamoto', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(38, 0, 0, 0, 'Yes', 'Kota', 'Furuhata', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(39, 0, 0, 0, 'Yes', 'Hiroto', 'Furuhata', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 7000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(40, 0, 0, 0, 'Yes', 'Aoto', 'Hironuma', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
INSERT INTO `students` VALUES(41, 0, 0, 0, 'Yes', 'Reiya', 'Nishimura', '', '', '', '', '', '', '0', '', '0000-00-00', 'male', '', '', 0, 0, '', 8000, 'Mon', '00:00:00', '', '');
you can try below to find the student didn't make payment in April:
SELECT a.sid, a.name, a.last_name
FROM students a LEFT JOIN (Select * from payments Where forMonth = 'Apr' and Year(`date`) = 2015) b ON students.sid = b.sid
WHERE a.Active = 'Yes' AND b.sid is NULL
I suggest to filter based on payment year as well if you got cumulative data.
SELECT a.sid, a.name, a.last_name
FROM students a LEFT JOIN (Select * from payments Where forMonth = 'Apr' and Year(`date`) = 2015) b ON students.sid = b.sid
WHERE a.Active = 'Yes' AND b.sid is NULL
I suggest to filter based on payment year as well if you got cumulative data.
ASKER
SELECT a.sid, a.name, a.last_name
FROM students a LEFT JOIN (Select * from payments Where forMonth = 'Apr') b ON students.sid = b.sid
WHERE a.Active = 'Yes' AND b.sid is NULLUnknown column 'students.sid' in 'on clause'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the queries postes here will help you, as they are correct. But I think it is important that you understand the original query you have written.
Basically, the query on your question says: join payments and students by sid and filter only the records that payment does not have occurred in April for active students.
So, as you can see, your query was not fitting your request.
Basically, the query on your question says: join payments and students by sid and filter only the records that payment does not have occurred in April for active students.
So, as you can see, your query was not fitting your request.
the queries postes here will help you, as they are correct. But I think it is important that you understand the original query you have written.
Basically, the query on your question says: join payments and students by sid and filter only the records that payment does not have occurred in April for active students.
So, as you can see, your query was not fitting your request.
Basically, the query on your question says: join payments and students by sid and filter only the records that payment does not have occurred in April for active students.
So, as you can see, your query was not fitting your request.
ASKER
I don't agree with you, Walter, as both of these guys were able to create what I was asking for.