Member Avatar for aadebayo

Hello

I have been using SQL for a number of years, but I am struggling with an intermediate query. I want a record to be displayed as below

1 	Ademola Adebayo 	4 	Women @ West Street 	Member
2 	Christine Adebayo 	4 	Women @ West Street 	Leader

when I run the following query

select `mu`.`id` AS `id`,`mu`.`firstname` AS `firstname`,`mu`.`lastname` AS `lastname`,`mu`.`memberTypeID` AS `memberTypeId`,`mm`.`name` AS `MinistryName`,`mmr`.`name` AS `RoleName` from (((`members_users` `mu` left join `members_ministryParticipant` `mmp` on((`mu`.`id` = `mmp`.`membersID`))) left join `members_Ministry` `mm` on((`mm`.`id` = `mmp`.`ministryID`))) left join `members_Ministry_Role` `mmr` on((`mmr`.`id` = `mmp`.`roleID`))) where (`mu`.`memberTypeID` > 1) and mm.name = 'Women @ West Street '
union
select `mu`.`id` AS `id`,`mu`.`firstname` AS `firstname`,`mu`.`lastname` AS `lastname`,`mu`.`memberTypeID` AS `memberTypeId`,`mm`.`name` AS `MinistryName`,`mmr`.`name` AS `RoleName` from (((`members_users` `mu` left join `members_ministryParticipant` `mmp` on((`mu`.`id` = `mmp`.`membersID`))) left join `members_Ministry` `mm` on((`mm`.`id` = `mmp`.`ministryID`))) left join `members_Ministry_Role` `mmr` on((`mmr`.`id` = `mmp`.`roleID`))) where (`mu`.`memberTypeID` > 1)
and mm.name = 'Women @ West Street' or mu.id in (select `mu`.`id` AS `id` from (((`members_users` `mu` left join `members_ministryParticipant` `mmp` on((`mu`.`id` = `mmp`.`membersID`))) left join `members_Ministry` `mm` on((`mm`.`id` = `mmp`.`ministryID`))) left join `members_Ministry_Role` `mmr` on((`mmr`.`id` = `mmp`.`roleID`))))

But what the above query returns is this

1 	Ademola Adebayo 	4 	Women @ West Street 	Member
1 	Ademola Adebayo 	4 	Mothers and Toddlers 	Member
2 	Christine Adebayo 	4 	Women @ West Street 	Leader

Please can any one tell me how to eliminate the second row?

Edited by Narue because: Limited query results per author request
  • 3 Contributors
  • 7 Replies
  • 185 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by peter_budo

Recommended Answers

All 7 Replies

Member Avatar for aadebayo

Further Info

Show table results for all tables

Below is the show table results for all tables involved

CREATE TABLE `members_users` (\n `id` int(10) NOT NULL,\n `memberTypeID` int(1) NOT NULL default '1',\n `housegroupID` int(2) default NULL,\n `firstname` varchar(30) NOT NULL,\n `lastname` varchar(30) NOT NULL,\n `SpouseName` varchar(20) default NULL,\n `SpouseDesc` varchar(10) default NULL,\n `gender` varchar(10) NOT NULL,\n `marital_status` varchar(10) default NULL,\n `email` varchar(50) default NULL,\n `busemail` varchar(50) default NULL,\n `homephone` varchar(15) default NULL,\n `businessphone` varchar(15) default NULL,\n `mobilephone` varchar(15) default NULL,\n `address1` varchar(30) NOT NULL,\n `address2` varchar(30) default NULL,\n `towns` varchar(30) default NULL,\n `postCode` varchar(10) NOT NULL,\n `nextkin` varchar(50) default NULL,\n `relationship` varchar(50) default NULL,\n `telkin` varchar(15) default NULL,\n `country` varchar(30) default NULL,\n `username` varchar(30) NOT NULL,\n `password` varchar(30) default NULL,\n `status` int(1) default '1',\n `registration_timestamp` int(20) default '0',\n `email_verify_code` varchar(12) default NULL,\n `dateOfBirth` date default NULL,\n `dateOfBaptism` date default NULL,\n `dateOfMembership` date default NULL,\n `dateOfTermination` date default NULL,\n `REASONID` decimal(10,0) default NULL,\n `TerminationReason` longtext,\n `CRBChecked` char(1) default NULL,\n `DateOfCRBCheck` date default NULL,\n `MinistryLeader` char(1) default NULL,\n UNIQUE KEY `id` (`id`),\n UNIQUE KEY `username` (`username`)\n) ENGINE=MyISAM AUTO_INCREMENT=122 DEFAULT CHARSET=latin1

CREATE TABLE `members_Ministry` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(50) NOT NULL,\n `description` longtext NOT NULL,\n `CRBRequired` char(1) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

CREATE TABLE `members_ministryParticipant` (\n `membersID` int(11) NOT NULL,\n `ministryID` int(11) NOT NULL,\n `roleID` int(11) default NULL,\n PRIMARY KEY (`membersID`,`ministryID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `members_Ministry_Role` (\n `id` int(11) NOT NULL auto_increment,\n `name` varchar(50) NOT NULL,\n `description` longtext NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE VIEW `vwMembersMinistryDetails` AS select `mu`.`id` AS `id`,`mu`.`firstname` AS `firstname`,`mu`.`CRBChecked` AS `CRBChecked`,`mu`.`lastname` AS `lastname`,`mu`.`memberTypeID` AS `memberTypeId`,`mm`.`name` AS `MinistryName`,`mmr`.`name` AS `RoleName` from (((`members_users` `mu` left join `members_ministryParticipant` `mmp` on((`mu`.`id` = `mmp`.`membersID`))) left join `members_Ministry` `mm` on((`mm`.`id` = `mmp`.`ministryID`))) left join `members_Ministry_Role` `mmr` on((`mmr`.`id` = `mmp`.`roleID`))) where (`mu`.`memberTypeID` > 1)

Desired result from query

SELECT * FROM vwMembersMinistryDetails where MinistryName = 'Mothers and Toddlers'
union
SELECT * FROM vwMembersMinistryDetails

id firstname CRBChecked lastname memberTypeId MinistryName RoleName

1 Ademola 0 Adebayo 4 Women @ West Street Member
2 Christine 1 Adebayo 4 NULL NULL

Current Result from query

SELECT * FROM vwMembersMinistryDetails where MinistryName = 'Mothers and Toddlers'
union
SELECT * FROM vwMembersMinistryDetails

id firstname CRBChecked lastname memberTypeId MinistryName RoleName

1 Ademola 0 Adebayo 4 Women @ West Street Member
2 Ademola 0 Adebayo 4 Mothers and Toddlers Member
2 Christine 1 Adebayo 4 NULL NULL
Edited by Narue because: Removed insert statements with sensitive data & limited query results
Member Avatar for smantscheff

Your query makes no sense, since it is an union of a set and a subset of this set.
This query will always return the whole set - the entire view.
The desired result makes no sense, either. What are the selection criteria so that user 1 (Ademole Adebayo) shall be listed with "Women @ West St.", but not with "Mothers and Toddlers"? This user has two roles in different ministries and is therefore listed twice. If she was to be listed only once, why with "Women @ West St." and not with "Mothers and Toddlers"?
Thanks a lot for submitting a complete test case right away.

Member Avatar for aadebayo

What I need the query for is that when I go into a ministry, show me the list of all members (members_users) both who have a role in this ministry (Mothers and Toddlers) and those who have no role in this Ministry. But do not display records of members more than once. Sorry, I made a mistake in my desired result. My desired result is

id firstname CRBChecked lastname memberTypeId MinistryName RoleName 
1 Ademola 0 Adebayo 4 Mothers and Toddlers 
2 Christine 1 Adebayo 4 NULL NULL
Edited by Narue because: Limited query results per author request
Member Avatar for smantscheff

So what you want is that user 1 (Ademole Adebayo) appears only once, but once for each ministry, which means twice. So you want user 1 to be displayed exactly once and exactly twice in the same query, which is impossible.
Maybe you would be happier using the group_concat function, with which you could group the result by user-ids and concatenate their memberships in a result field, like in

select id,firstname,lastname,group_concat(ministryname) from vwMembersMinistryDetails group by id
commented: Very good suggestion. +1
Member Avatar for aadebayo

Thanks very much, it worked

Member Avatar for aadebayo

How can I edit the previous posts that has data in it? It contains data that is not totally encrypted.

Edited by aadebayo because: n/a
Member Avatar for peter_budo

How can I edit the previous posts that has data in it? It contains data that is not totally encrypted.

Are you talking about editing posts that you created on this forum? If so then you cannot edit post after 30 min since you post it. Therefore you will not be able to change it now. If you want to remove some data from previous posts you can send me PM(personal message) and I can do it for you.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.