3

I'm trying to learn how to do mysql joins. Twitter has helped already get me started.

I'm trying to find the number of members that have entries attached to them...

Then the number of members that have entries with a specific category_id attached to them.

What I have so far.

{exp:query sql="
    SELECT count(*) AS num_members
    FROM exp_members m
    JOIN exp_channel_titles t on t.author = m.member_id"}

    {num_members}

{/exp:query}
Improve this question
  • What's the error you are getting now? That looks right to me, though I may be missing something obvious. – Adrian Macneil Jan 11 '13 at 1:54
  • Unknown column 't.author' in 'on clause' – Jake Chapman Jan 11 '13 at 1:56
  • Ok, here is the final code. Makes way more sense now actually seeing code that works the way I wanted it. Thanks you guys for clearing this up. {exp:query sql=" SELECT count(*) AS num_members FROM exp_members m JOIN exp_channel_titles t on t.author_id = m.member_id Where channel_id='4' AND status='Goal_set' "} {num_members} {/exp:query} – Jake Chapman Jan 11 '13 at 2:12
3

Shouldn't it be t.author_id instead of t.author?

{exp:query sql="
    SELECT count(*) AS num_members
    FROM exp_members m
    JOIN exp_channel_titles t on t.author_id = m.member_id"}

    {num_members}

{/exp:query}
Improve this answer
  • 1
    Nice catch! I'll edit your answer to add the complete solution. – Adrian Macneil Jan 11 '13 at 2:16

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.