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.

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.
kaileeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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!
kaileeAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

kaileeAuthor 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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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
Julian HansenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter RitzelSenior Software EngineerCommented:
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 EngineerCommented:
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.
kaileeAuthor Commented:
I don't agree with you, Walter, as both of these guys were able to create what I was asking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.