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
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.forMonth
oddly, other students have also paid for april, but they don't show up in the list.
any help would be greatly appreciated.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
LVL 111
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!
-- 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.
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
Unknown column 'students.sid' in 'on clause'
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
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
You can try this query.
The query looks for all students that don't have a payment record for April
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'
);
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
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.
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.