Answers.Body: <p>Please use the below SQL to list all the membership related to single membership in an single row - I guess this helps</p>
  
  <p>select 
  GROUP_CONCAT(b.name) 
  from civicrm_membership a,civicrm_membership_type b where b.id = a.membership_type_id
  and a.contact_id in (select contact_id from civicrm_membership GROUP BY contact_id having COUNT(*) > 1)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-30T02:23:51.517Z
Answers.Text: I wasn't very clear.   I need to list all memberships for a Client/Contact in one row.   I need client name, membership type(s) and start/end date.  If you get me started I think I can figure out the rest.
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-30T08:34:56.720Z
Answers.Text: SELECT (select display_name from civicrm_contact where id =  a.contact_id) as company_name, GROUP_CONCAT(b. NAME),GROUP_CONCAT(a.start_date),GROUP_CONCAT(a.end_date) 
  FROM  civicrm_membership a,  civicrm_membership_type b
  WHERE b.id = a.membership_type_id 
  AND a.contact_id IN ( SELECT contact_id FROM civicrm_membership GROUP BY contact_id ) GROUP BY a.contact_id
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-30T08:37:08.767Z
Answers.Text: above script will list company name, all membership type , start_date, end_date - please test the script and let me know the status - thanks
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-30T13:58:51.887Z
Answers.Text: so far so good.  I just installed MySQL Workbench and ran this.  Looks good!  Greatly appreciate the help!
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-30T20:15:06.570Z
Answers.Text: Where in the select would I put a where clause to grab just the Membership Status of 1,2 or 3 (New, Current, Grace)
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-02T08:36:13.580Z
Answers.Text: SELECT (SELECT display_name FROM civicrm_contact WHERE id = a.contact_id ) AS company_name, GROUP_CONCAT(b. NAME), GROUP_CONCAT(a.start_date), GROUP_CONCAT(a.end_date) FROM civicrm_membership a, civicrm_membership_type b WHERE b.id = a.membership_type_id AND a.status_id IN (1,2,3) AND a.contact_id IN ( SELECT contact_id FROM civicrm_membership GROUP BY contact_id ) GROUP BY a.contact_id - I have updated the SQL
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-13T21:19:24.450Z
Answers.Text: Excellent, this is a big help.  I may have another question or two but this gets me started.
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-14T09:19:06.243Z
Answers.Text: please drop your question let me see what i can do
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-16T02:52:50.087Z
Answers.Text: Here's a list of fields I need for the query.  Can these all be added?   First Name(already have),
  Middle Name,
  Last Name(already have),
  Job Title,
  Current Employer,
  Street Address,
  Supplemental Address 1,
  City,
  State,
  Postal Code,
  Phone,
  Email,
  Chapter Membership Type(already have),
  Membership Type(already have),
  Member Since Date,
  Membership Expiration Date(already have),
  Membership Status,
  Total Amount,
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-20T00:58:45.800Z
Answers.Text: I got the Middle Name and Job Title.  See previous post.... I'm not sure how to get the Current Employer, Member Since, Membership Status, and total amount.
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-20T09:07:35.127Z
Answers.Text: hi Gary, I have created the SQL but it is too long for the comment so i am posting it as an answer - if you have any issues please let me know - thanks
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-09-29T13:54:24.220Z
Answers.LastActivityDate: 2015-09-29T13:54:24.220Z
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.Body: <pre><code>SELECT
  membership.id,
  contact.first_name,
  contact.middle_name,
  contact.last_name,
  contact.job_title,
  contact.organization_name,
  address.street_name,
  address.supplemental_address_1,
  (
      SELECT
          NAME
      FROM
          civicrm_state_province
      WHERE
          id = address.state_province_id
  ) AS state,
  address.city,
  address.postal_code,
  phone.phone,
  GROUP_CONCAT(mem_type. NAME),
  GROUP_CONCAT(membership.join_date) AS since_date,
  GROUP_CONCAT(membership.start_date),
  GROUP_CONCAT(membership.end_date),
  (
      SELECT
          NAME
      FROM
          civicrm_membership_status
      WHERE
          id = GROUP_CONCAT(membership.status_id)
  ) AS membership_status,
  
  contribution.total_amount,
  line_item.qty,
  line_item.unit_price,
  line_item.line_total,
  line_item.label,
  email.email
  FROM
  civicrm_membership membership
  LEFT JOIN civicrm_membership_type mem_type ON mem_type.id = membership.membership_type_id
  LEFT JOIN civicrm_contact contact ON contact.id = membership.contact_id
  LEFT JOIN civicrm_address address ON address.contact_id = contact.id AND address.is_primary = 1
  LEFT JOIN civicrm_phone phone ON phone.contact_id = contact.id AND phone.is_primary = 1
  LEFT JOIN civicrm_membership_payment payment ON payment.membership_id = membership.id
  LEFT JOIN civicrm_contribution contribution ON contribution.id = payment.contribution_id
  LEFT JOIN civicrm_email email ON email.contact_id = contact.id AND email.is_primary = 1
  LEFT JOIN civicrm_line_item line_item ON  line_item.entity_id = membership.id 
  WHERE
  membership.status_id IN (1, 2, 3)
  AND membership.contact_id IN (
  SELECT
      contact_id
  FROM
      civicrm_membership
  GROUP BY
      contact_id
  )
  GROUP BY
  membership.contact_id
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-20T17:44:58.260Z
Answers.Text: Excellent... this is amazing!   Is there a way to get email address?   Also, is it possible to list the items in a price in the contribution?  Some of the Contributions have a price set for the memberships.  See below:Payment Amount 
  Item Qty Unit Price Total Price
  Dues - Professional 1 $ 290.00 $ 290.00
  Chapter Dues - New England 1 $ 35.00 $ 35.00
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-21T11:17:59.750Z
Answers.Text: hi Gary, I have updated the SQL please give a test - thanks
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-21T20:11:39.980Z
Answers.Text: the values in the line item are not right.... bad part is that my data is not consistent to test this.  The line items are not in every membership because some I had to import.  As new members sign up then they use the price set(line items) to purchase the membership.  I  think the line items would need to be concatenated because there could be more than one per membership.  Also when you made the change the current ConCat fields duplicated themselves. In the Mem Type.Name field, instead of "Professional, Oregon" it now has "Oregon, Professional, Oregon, Professional"
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-22T08:28:17.050Z
Answers.Text: ok sorry about that but without seeing the data its bit hard to create a SQL - please remove civicrm_line_item - and i guess rest should work correctly for your requirement - let me see what i can do to list the line item
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-23T15:59:00.077Z
Answers.Text: ok... I removed the line item pieces.  Working ok but not getting Total Amount from the Contribution.  I can send you sample data/screen shots.... just need a way to get them to you.
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-23T16:22:30.147Z
Answers.Text: Please send me the sample data to ramesh@millertech.co.uk so i can have a look at the data and will create the SQL
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-26T00:19:29.190Z
Answers.Text: sent.  Let me know if you need anything else.
Answers.UserDisplayName: Gary Zimmerman
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-20T09:17:25.280Z
Answers.LastActivityDate: 2015-10-26T11:46:21.827Z
Answers.LastEditDate: 2015-10-26T11:46:21.827Z
Answers.UserDisplayName: Ramesh - ARTECH Consultancy
Body: <p>CiviCRM 4.6.8 and WP 4.3.1
  Many of our members have two memberships, a main membership (ie. Professional or Student) and a State(or area) Chapter membership (ie. California or New England, etc.).   How can I report in a single line what memberships a member has?  Do I need to do this in SQL or can I do this in a native CiviReport.   If SQL could someone get me started.  THANKS.</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-09-29T13:29:33.750Z
Comments.Text: how many membership do you have for an single contact is it always 2 membership ??
Comments.UserDisplayName: Ramesh - ARTECH Consultancy
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-09-30T02:13:13.697Z
Comments.Text: typically two but sometimes just one or it could be many.  We have 17 chapters that an individual could become a member of!
Comments.UserDisplayName: Gary Zimmerman
Tags: <civireport>
Title: Report of multiple different memberships per contact in one line
ThreadText: # Report of multiple different memberships per contact in one line
  
  (Asked by: username_0 on 2015-09-29)
  
  CiviCRM 4.6.8 and WP 4.3.1
  Many of our members have two memberships, a main membership (ie. Professional or Student) and a State(or area) Chapter membership (ie. California or New England, etc.). How can I report in a single line what memberships a member has? Do I need to do this in SQL or can I do this in a native CiviReport. If SQL could someone get me started. THANKS.
  
  **Tags:** civireport
  
  > **Comments:**
  > **username_1**: how many membership do you have for an single contact is it always 2 membership ??  
  > **username_0**: typically two but sometimes just one or it could be many.  We have 17 chapters that an individual could become a member of!  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2015-09-29. Score: 2 | Accepted answer
  
  Please use the below SQL to list all the membership related to single membership in an single row - I guess this helps
  
  select
  GROUP\_CONCAT(b.name)
  from civicrm\_membership a,civicrm\_membership\_type b where b.id = a.membership\_type\_id
  and a.contact\_id in (select contact\_id from civicrm\_membership GROUP BY contact\_id having COUNT(\*) > 1)
  
  > **Comments:**
  > **username_0**: I wasn't very clear.   I need to list all memberships for a Client/Contact in one row.   I need client name, membership type(s) and start/end date.  If you get me started I think I can figure out the rest.  
  > **username_1**: SELECT (select display_name from civicrm_contact where id =  a.contact_id) as company_name, GROUP_CONCAT(b. NAME),GROUP_CONCAT(a.start_date),GROUP_CONCAT(a.end_date)  FROM  civicrm_membership a,  civicrm_membership_type b WHERE b.id = a.membership_type_id  AND a.contact_id IN ( SELECT contact_id FROM civicrm_membership GROUP BY contact_id ) GROUP BY a.contact_id  
  > **username_1**: above script will list company name, all membership type , start_date, end_date - please test the script and let me know the status - thanks  
  > **username_0**: so far so good.  I just installed MySQL Workbench and ran this.  Looks good!  Greatly appreciate the help!  
  > **username_0**: Where in the select would I put a where clause to grab just the Membership Status of 1,2 or 3 (New, Current, Grace)  
  > **username_1**: SELECT (SELECT display_name FROM civicrm_contact WHERE id = a.contact_id ) AS company_name, GROUP_CONCAT(b. NAME), GROUP_CONCAT(a.start_date), GROUP_CONCAT(a.end_date) FROM civicrm_membership a, civicrm_membership_type b WHERE b.id = a.membership_type_id AND a.status_id IN (1,2,3) AND a.contact_id IN ( SELECT contact_id FROM civicrm_membership GROUP BY contact_id ) GROUP BY a.contact_id - I have updated the SQL  
  > **username_0**: Excellent, this is a big help.  I may have another question or two but this gets me started.  
  > **username_1**: please drop your question let me see what i can do  
  > **username_0**: Here's a list of fields I need for the query.  Can these all be added?   First Name(already have), Middle Name, Last Name(already have), Job Title, Current Employer, Street Address, Supplemental Address 1, City, State, Postal Code, Phone, Email, Chapter Membership Type(already have), Membership Type(already have), Member Since Date, Membership Expiration Date(already have), Membership Status, Total Amount,  
  > **username_0**: I got the Middle Name and Job Title.  See previous post.... I'm not sure how to get the Current Employer, Member Since, Membership Status, and total amount.  
  > **username_1**: hi Gary, I have created the SQL but it is too long for the comment so i am posting it as an answer - if you have any issues please let me know - thanks  
  
  ---
  
  ### Answer by username_1 on 2015-10-20. Score: 1
  
  SELECT
  membership.id,
  contact.first_name,
  contact.middle_name,
  contact.last_name,
  contact.job_title,
  contact.organization_name,
  address.street_name,
  address.supplemental_address_1,
  (
      SELECT
          NAME
      FROM
          civicrm_state_province
      WHERE
          id = address.state_province_id
  ) AS state,
  address.city,
  address.postal_code,
  phone.phone,
  GROUP_CONCAT(mem_type. NAME),
  GROUP_CONCAT(membership.join_date) AS since_date,
  GROUP_CONCAT(membership.start_date),
  GROUP_CONCAT(membership.end_date),
  (
      SELECT
          NAME
      FROM
          civicrm_membership_status
      WHERE
          id = GROUP_CONCAT(membership.status_id)
  ) AS membership_status,
  
  contribution.total_amount,
  line_item.qty,
  line_item.unit_price,
  line_item.line_total,
  line_item.label,
  email.email
  FROM
  civicrm_membership membership
  LEFT JOIN civicrm_membership_type mem_type ON mem_type.id = membership.membership_type_id
  LEFT JOIN civicrm_contact contact ON contact.id = membership.contact_id
  LEFT JOIN civicrm_address address ON address.contact_id = contact.id AND address.is_primary = 1
  LEFT JOIN civicrm_phone phone ON phone.contact_id = contact.id AND phone.is_primary = 1
  LEFT JOIN civicrm_membership_payment payment ON payment.membership_id = membership.id
  LEFT JOIN civicrm_contribution contribution ON contribution.id = payment.contribution_id
  LEFT JOIN civicrm_email email ON email.contact_id = contact.id AND email.is_primary = 1
  LEFT JOIN civicrm_line_item line_item ON  line_item.entity_id = membership.id 
  WHERE
  membership.status_id IN (1, 2, 3)
  AND membership.contact_id IN (
  SELECT
      contact_id
  FROM
      civicrm_membership
  GROUP BY
      contact_id
  )
  GROUP BY
  membership.contact_id
  
  > **Comments:**
  > **username_0**: Excellent... this is amazing!   Is there a way to get email address?   Also, is it possible to list the items in a price in the contribution?  Some of the Contributions have a price set for the memberships.  See below:Payment Amount  Item Qty Unit Price Total Price Dues - Professional 1 $ 290.00 $ 290.00 Chapter Dues - New England 1 $ 35.00 $ 35.00  
  > **username_1**: hi Gary, I have updated the SQL please give a test - thanks  
  > **username_0**: the values in the line item are not right.... bad part is that my data is not consistent to test this.  The line items are not in every membership because some I had to import.  As new members sign up then they use the price set(line items) to purchase the membership.  I  think the line items would need to be concatenated because there could be more than one per membership.  Also when you made the change the current ConCat fields duplicated themselves. In the Mem Type.Name field, instead of "Professional, Oregon" it now has "Oregon, Professional, Oregon, Professional"  
  > **username_1**: ok sorry about that but without seeing the data its bit hard to create a SQL - please remove civicrm_line_item - and i guess rest should work correctly for your requirement - let me see what i can do to list the line item  
  > **username_0**: ok... I removed the line item pieces.  Working ok but not getting Total Amount from the Contribution.  I can send you sample data/screen shots.... just need a way to get them to you.  
  > **username_1**: Please send me the sample data to ramesh@millertech.co.uk so i can have a look at the data and will create the SQL  
  > **username_0**: sent.  Let me know if you need anything else.  
  
  ---
  
  