mysql query to show non-payments for specific month(s)

kailee
kailee used Ask the Experts™
on
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.

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.forMonth

Open in new window


oddly, other students have also paid for april, but they don't show up in the list.

any help would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Ray Paseur
Most Valuable Expert 2011
Top Expert 2016

Commented:
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!
kailee

Author

Commented: 
-- 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);

Open in new window


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', '', '');

Open in new window

Ryan ChongSoftware Team Lead

Commented:
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.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Read More
kailee

Author

Commented: 
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 NULL

Open in new window

I tried this, removing the date  portion as I don't have that built in yet) and got:
Unknown column 'students.sid' in 'on clause'
Ryan ChongSoftware Team Lead
Commented:
opps, seems i'm using alias, pls replace "students" with "a", try:

SELECT a.sid, a.name, a.last_name
FROM students a LEFT JOIN (Select * from payments Where forMonth = 'Apr') b ON a.sid = b.sid
WHERE a.Active = 'Yes' AND b.sid is NULL
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
You can try this query.

The query looks for all students that don't have a payment record for April

SELECT * FROM students 
WHERE sid NOT IN (
  SELECT sid FROM payments WHERE forMonth = 'Apr'
);

Open in new window

Walter RitzelSenior Software Engineer

Commented:
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.
Walter RitzelSenior Software Engineer

Commented:
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.
kailee

Author

Commented:
I don't agree with you, Walter, as both of these guys were able to create what I was asking for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial