0

I am trying to limit the following query by the member's group id, but having some difficulty with the join statement.

This query works fine:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
FROM exp_member_data WHERE m_field_id_6 != ''
AND m_field_id_9 = 'USA'
GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC
"}
  {state} ({member_count})<br />
{/exp:query}

But I am struggling with how to limit by group_id:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
FROM exp_member_data WHERE m_field_id_6 != ''
AND m_field_id_9 = 'USA'
JOIN exp_members ON exp_members.member_id = exp_member_data.member_id
WHERE exp_members.group_id='5'
GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC
"}
  {state} ({member_count})<br />
{/exp:query}

The end result would be a directory of members grouped by state. I've maxed out my query knowledge, thank you for any assistance or suggestions.

2

You can try this query:

{exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state
        FROM exp_member_data 
        JOIN exp_members ON exp_members.member_id = exp_member_data.member_id
        WHERE m_field_id_6 != '' 
        AND m_field_id_9 = 'USA'
        AND exp_members.group_id='5'
        GROUP BY m_field_id_6 ORDER BY m_field_id_6 ASC"}
    {state} ({member_count})<br />
{/exp:query}

Plz share if ORDER BY is also working fine.

1
  • Thank you Bhashkar, It works exactly as expected. The results are displaying in alphabetical order with the test data, so ORDER BY looks fine so far. Very much appreciated. – oliverb May 28 '13 at 20:38

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.