CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • A query to find duplicate current memberships
Pages: [1]

Author Topic: A query to find duplicate current memberships  (Read 1292 times)

CiviTeacher.com

  • I live on this forum
  • *****
  • Posts: 1282
  • Karma: 118
    • CiviTeacher
  • CiviCRM version: 3.4 - 4.5
  • CMS version: Drupal 6&7, Wordpress
  • MySQL version: 5.1 - 5.5
  • PHP version: 5.2 - 5.4
A query to find duplicate current memberships
July 21, 2010, 12:51:56 pm
I have written this simple query:

Code: [Select]
SELECT contact_id, count(contact_id) AS cnt, civicrm_contact.display_name
FROM civicrm_membership
JOIN civicrm_contact ON civicrm_contact.id=civicrm_membership.contact_id
WHERE status_id < 3 GROUP BY contact_id ORDER BY cnt ASC;

It searches and returns any contact that has more than one membership that is 'current'.  Because of the way advanced search works with a lot of "OR" (as opposed to "AND") searching on the memberships, I could not find a way with existing functionality to gather the information on duplicate memberships that I needed.

If someone finds this useful (besides me) please maybe we could consider integrating this into the search capabilities.
Try CiviTeacher: the online video tutorial CiviCRM learning library.

petednz

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4899
  • Karma: 193
    • Fuzion
  • CiviCRM version: 3.x - 4.x
  • CMS version: Drupal 6 and 7
Re: A query to find duplicate current memberships
July 21, 2010, 02:33:26 pm
Great - we are just about to do similar to find those who have a Current plus a Pending - so will add that back in to the mix.
Sign up to StackExchange and get free expert advice: https://civicrm.org/blogs/colemanw/get-exclusive-access-free-expert-help

pete davis : www.fuzion.co.nz : connect + campaign + communicate

CiviTeacher.com

  • I live on this forum
  • *****
  • Posts: 1282
  • Karma: 118
    • CiviTeacher
  • CiviCRM version: 3.4 - 4.5
  • CMS version: Drupal 6&7, Wordpress
  • MySQL version: 5.1 - 5.5
  • PHP version: 5.2 - 5.4
Re: A query to find duplicate current memberships
July 21, 2010, 08:48:29 pm
Cool, should be easy enough to add:  AND status_id = X

where X is the status of a pending memberships

Glad you can use....cheers
Try CiviTeacher: the online video tutorial CiviCRM learning library.

cognoscento

  • I post occasionally
  • **
  • Posts: 44
  • Karma: 1
  • CiviCRM version: 4.2
  • CMS version: Drupal 7.17
  • MySQL version: 5.1.65
  • PHP version: 5.3.17
Re: A query to find duplicate current memberships
January 06, 2011, 08:49:00 am
I'm looking to do exactly this... how can I build a custom search around that query, or one very similar? On first blush the process looks a little daunting for a non-coder...

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: A query to find duplicate current memberships
January 06, 2011, 10:09:38 am

instructions for writing a custom search here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

Knowledge of PHP and MySQL is needed

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • A query to find duplicate current memberships

This forum was archived on 2017-11-26.