1
SELECT `member_name.smf_members`, COUNT(`author.smf_links`) 
FROM smf_members, smf_links
JOIN smf_links ON id_member.smf_members = author.smf_links;
GROUP BY `author.smf_links`

It says smf_links is not a unique table/alias. All I want to do is make it get a count of how many rows has the same author, and show the member name instead of the author (which is a number) and the count.

So instead of: 1 | 23

It would show: Bunny | 23

I can't figure this out and my brain has gone numb from googling. Can someone please help me out? :)

Jade Elizabeth
  • 133
  • 8
  • `member_name.smf_members` parameter will show probably Bunny, isn't it? Maybe member_id or something will solve your problem – SerhatCan Jul 18 '14 at 07:30
  • Do you really need to join with `smf_links` twice? You have both an implicit join and an explicit join with the same table. – Barmar Jul 18 '14 at 07:31
  • I only want to show the name though, not the id. The id is what I want to join on. Barmar what do you mean? – Jade Elizabeth Jul 18 '14 at 07:35
  • You have `FROM smf_members, smf_links JOIN smf_links`. The first one is an implicit join, the second one is an explicit join. – Barmar Jul 18 '14 at 07:36

1 Answers1

4

The error you're getting is because you're joining with the same table, smf_links, twice. If that's what you really want to do, you need to give them aliases so you can distinguish them. But I suspect you did that by mistake.

Also, the syntax is tablename.column, not column.table. And you have to put the backticks around each part separately; if the . is inside the backticks, it's treated as a literal character, not a delimiter.

SELECT `smf_members`.`member_name`, COUNT(*)
FROM smf_members
JOIN smf_links ON smf_members.id_member = smf_links.author
GROUP BY smf_links.author
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • That got it, thank you! Not entirely sure how it knew to count the links table rows. Would you mind explaining that? :) – Jade Elizabeth Jul 18 '14 at 07:51
  • `JOIN` creates a cross-product, and `COUNT(*)` counts the rows in that cross-product. You don't need to put a column name in `COUNT()` unless you need to not count rows where that column is `NULL`. – Barmar Jul 18 '14 at 07:53
  • Oh, right, thank you so much! I am a little rusty I admit. Still thinking of things too rigidly/literally. – Jade Elizabeth Jul 18 '14 at 08:20