MySQL Forums
Forum List  »  Newbie

Is it possible to pass parameters to subqueries
Posted by: Michael Hendry
Date: October 10, 2011 02:24PM

I maintain records for a club website, which displays a list of members for the current session, with details of committee membership etc, including the date of joining, and which can display the corresponding information for previous years.

tbl_members holds the basic information about individual members, and tbl_membership_status dates of joining and leaving (and a number of other possibilities, not relevant to this question).

My problem arises because it is possible for a member to move away and leave the club, and then come back again, and I'm having difficulty in finding the earliest joining date which is after the most recent leaving date (the member called "Returning Member" below illustrates this).

--
-- Table structure for table `tbl_members`
--

DROP TABLE IF EXISTS `tbl_members`;
CREATE TABLE IF NOT EXISTS `tbl_members` (
  `member_id` bigint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`member_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 PACK_KEYS=0 AUTO_INCREMENT=3 ;


--
-- Dumping data for table `tbl_members`
--

INSERT INTO `tbl_members` (`member_id`, `name`) VALUES
(1, 'Member'),
(2, 'Returning Member');

-- --------------------------------------------------------

--
-- Table structure for table `tbl_membership_status`
--

DROP TABLE IF EXISTS `tbl_membership_status`;
CREATE TABLE IF NOT EXISTS `tbl_membership_status` (
  `membership_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `member_key` bigint(20) NOT NULL DEFAULT '0',
  `membership_status` enum('ordinary_member','past_member') NOT NULL DEFAULT 'ordinary_member',
  `start_date` date NOT NULL DEFAULT '0000-00-00',
  UNIQUE KEY `membership_id` (`membership_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `tbl_membership_status`
--

INSERT INTO `tbl_membership_status` (`membership_id`, `member_key`, `membership_status`, `start_date`) VALUES
(1, 1, 'ordinary_member', '2000-01-01'),
(2, 2, 'ordinary_member', '1989-10-18'),
(3, 2, 'past_member', '1990-07-01'),
(4, 2, 'ordinary_member', '2008-01-16');

You'll see from tbl_membership_status, that "Returning Member" joined on 18th October 1989, but left shortly afterwards on 1st July 1990. He re-joined on 16th January 2008.

I want "Returning Member" to be reported as joining on 16th Jan 2008 as far as the current club year is concerned (1st July 2011 to 30th June 2012 inclusive), to disappear as the years are clocked backwards to 1st July 2006, and to reappear again when the calendar gets back to 1st July 1989 with his original joining date of 18th October 1989.

The following code is the latest in a series of failed experiments! All the dates apart from "1932-01-01" (which was before the club's foundation) would be inserted into the real query by PHP, which adjusts them accordingly.

SELECT name, MIN( s.start_date )
FROM tbl_members m
LEFT JOIN tbl_membership_status s ON member_id = member_key
AND (
s.start_date >= "2011-07-01"
AND s.start_date < "2012-07-01"
AND s.membership_status = 'ordinary_member'
)
OR (
s.start_date < "2011-07-01"
AND (
s.start_date > (

SELECT CASE (

SELECT COUNT( start_date )
FROM tbl_membership_status
WHERE member_key = member_id
AND membership_status = 'past_member'
AND start_date < "2011-07-01"
ORDER BY start_date DESC
LIMIT 1
)
WHEN 1
THEN (

SELECT start_date
FROM tbl_membership_status
WHERE member_key = member_id
AND membership_status = 'past_member'
AND start_date < "2011-07-01"
ORDER BY start_date DESC
LIMIT 1
)
WHEN 0
THEN "1932-01-01"
END
)
)
AND membership_status = 'ordinary_member'
)
GROUP BY name
ORDER BY name

In the "SELECT CASE..." section, I can determine whether there's at least one past_member entry, and I'm trying to find the date of it in "THEN ( SELECT start_date...".

I think the reason I'm not getting the result I'm looking for is that the latter subquery doesn't "know" which member_id I'm interested in.

I might be forced to pass a lot more data back to PHP and sort the problem out there, but I'd much prefer to let MySQL do the donkey work and use PHP to provide the user interface, set up the queries and deal with presentation.

Many thanks in advance for any help!

Options: ReplyQuote


Subject
Written By
Posted
Is it possible to pass parameters to subqueries
October 10, 2011 02:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.