0

I have two tables members and fees ,fees table has a foreign key memberid which is a primary key in members . I want to get those members who have not yet paid. When a member pays there are two fields fromdate and todate and both they are datetime. So those fields helps us to know that the member paid from which date to which date . So I want to pull out those members whose todate is older than the current date , remember since they pay monthly I want to pull out only one latest todate for each member then I compare it with current date . Any help will be appreciated . bellow is what I have tried .

 select  members.name , fees.todate ,
    fees.usercode
    from members 
    INNER JOIN fees
    ON members.memberid  =  fees.memberid
    WHERE
     fees.usercode ='KMTS472895'
    AND 
    fees.todate < curdate() 
    AND 
    fees.todate = (SELECT MAX(fees.todate))
    AND 
    todate !='0000-00-00 00:00:00'
    GROUP BY fees.memberid
    ORDER BY fees.memberid

///////////////////////////////////////////

CREATE TABLE `members` (
  `memberid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `companyid` int(11) unsigned DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `initials` varchar(5) NOT NULL,
  `surname` varchar(30) NOT NULL,
  `datejoined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `usercode` varchar(12) NOT NULL,
  `address` varchar(50) DEFAULT NULL,
  `cell` varchar(15) DEFAULT NULL,
  `cell2` varchar(15) DEFAULT NULL,
  `pic` varchar(100) DEFAULT NULL,
  `idnumber` varchar(30) DEFAULT NULL,
  `rank` varchar(30) DEFAULT NULL,
  `gender` varchar(12) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `address2` varchar(50) DEFAULT NULL,
  `branch` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `Company_name` varchar(100) DEFAULT NULL,
  `website` varchar(100) DEFAULT NULL,
  `Comments` varchar(500) DEFAULT NULL,
  `Company_reg_number` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `next_of_kin` varchar(100) DEFAULT NULL,
  `next_of_kin_number` varchar(100) DEFAULT NULL,
  `patient_code` varchar(50) DEFAULT NULL,
  `medNumber` varchar(100) DEFAULT NULL,
  `medPlan` varbinary(100) DEFAULT NULL,
  PRIMARY KEY (`memberid`),
  KEY `companyid` (`companyid`),
  CONSTRAINT `members_ibfk_1` FOREIGN KEY (`companyid`) REFERENCES `company` (`companyid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1;

///////////////////////////////////////////////////



CREATE TABLE IF NOT EXISTS `fees` (
  `feesid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `feestype` varchar(50) NOT NULL,
  `amountpaid` decimal(10,2) DEFAULT NULL,
  `memberid` int(11) unsigned DEFAULT NULL,
  `companyid` int(11) unsigned DEFAULT NULL,
  `paidfor` varchar(100) NOT NULL,
  `paiddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `fromdate` datetime NOT NULL,
  `todate` datetime NOT NULL,
  `usercode` varchar(12) NOT NULL,
  `paidby` varchar(30) NOT NULL,
  `recieved_by` varchar(30) NOT NULL,
  `qty` int(5) NOT NULL,
  PRIMARY KEY (`feesid`),
  KEY `memberid` (`memberid`),
  KEY `companyid` (`companyid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1595 ;
1

Try this

select  members.name , fees.todate ,
fees.usercode
from members
INNER JOIN 
(
select max(usercode) as usercode
, memberid
, max(todate) as todate
from
fees    
WHERE
 fees.usercode ='KMTS472895'
 GROUP BY fees.memberid
) as fees
on members.memberid = fees.memberid
where fees.todate < curdate()
order by members.memberid
  • 1
    I appreciate but it did not work . it displays nothing – humphrey Sep 23 '17 at 18:59
  • Remember user code is an id for multi tenant db to Identify the client who is using the system – humphrey Sep 23 '17 at 19:00
  • 1
    Sorry, I forget to add the group by in the Sub-Query. I have updated my answer. – Artashes Khachatryan Sep 23 '17 at 19:05
  • look what it does , it take out all names and it gives them the same todate – humphrey Sep 23 '17 at 19:14
  • 1
    max(usercode) is used because of Group By. Whenever we do grouping we must use an aggregation function for the columns that don`t appear in the group by list. In this case we can use whatever we want, like max, min, etc. Can you please add table creation scripts to your question and also a script for populating some dummy data. It will help to understand your needs much better. – Artashes Khachatryan Sep 23 '17 at 19:44
1

Setup tables and some sample data:

NOTE: Was having some problems with (fiddle) table names @ rextester.com so added 'X' suffix to table names to get this to work.

drop table if exists feesX;
drop table if exists membersX;

create table membersX
(memberid    int
,name        varchar(100));

create table feesX
(memberid    int
,usercode    varchar(30)
,fromdate    datetime
,todate      datetime);

insert into membersX (memberid, name) values
(1,'John Doe'), 
(2,'Jane Doe'), 
(3,'Curly');

insert into feesX (memberid, usercode, fromdate, todate) values
(1,'XYZ1234567','2017-07-03','2017-07-22'),
(1,'KMTS472895','2017-07-23','2017-08-25'),
(1,'KMTS472895','2017-08-26','2017-10-11'),

(2,'XYZ1234567','2017-07-16','2017-08-21'),
(2,'KMTS472895','2017-08-22','2017-09-17');

Proposed query:

-- find all members who have at least 1 matching record in fees,
-- but also no fees records with todate >= curdate():

select   m.name,
         max(f.todate) as 'todate',
         f.usercode

from     membersX  m
join     feesX f

on       m.memberid  = f.memberid
where    f.usercode  = 'KMTS472895'
and      f.todate    < curdate()
and      f.todate   != '0000-00-00 00:00:00'

         -- only want members who don't have a fees record with todate >= curdate():

and      not exists(select 1
                    from   feesX f2
                    where  f2.memberid = m.memberid
                    and    f2.todate   >= curdate())

group by m.name, 
         f.usercode

union

-- add members with no record in fees (ie, have never paid)

select   m.name,
         NULL as 'todate',
         'KMTS472895' as 'usercode'

from     membersX  m

         -- no matching record in fees:

where    not exists(select 1
                    from   feesX f2
                    where  f2.memberid = m.memberid)

order by 1;

Results:

name      todate               usercode
--------- -------------------- ----------
Curly     NULL                 KMTS472895
Jane Doe  17.09.2017 00:00:00  KMTS472895

Here's a rextester fiddle

  • I am a bit confused its a little complex – humphrey Sep 23 '17 at 20:18
  • I have updated the fees table please check it – humphrey Sep 23 '17 at 20:24
  • first half of union uses your join criteria to find all members with a matching record in fees, and the sub-query ensures we're only interested in members who don't also have a matching record in fees with todate >= curdate() (ie, their latest todate is less than today); since a member may have multiple records in fees with todate < today, the max(f.todate) gives us the latest todate; the second half of the union lists members who have never paid a fee (it's not mentioned in your post if a member record can have no matching record in fees or if we should list it) – markp Sep 23 '17 at 20:24
  • you explained so well , but can u please update the answer also consideringing that the user code will not be the one in the query but it will be a variable from php like where usercode ="$usercode" – humphrey Sep 23 '17 at 20:30
  • 1
    1) I don't work with php so 2) I have no idea how you're building your query in php ... prepared statement? concatenating text of query with text of $usercode? a reference to the $usercode variable?; how are you passing $usercode into your query now? (I wouldn't expect it be to any different than what you're doing now ... ???) if you need help with php building the query then I'd suggest you post a new question, something along the lines of how to pass a php variable to a SQL query – markp Sep 23 '17 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.