Mysql Count with Inner join of two tables, Average Join

Question

Im eager to join two tables but can´t seem to get it right, the structure look like this.

Table1 (members) [UserLogin | Name ]

Table 2 (articles) [ArtID | Writer | Content] Article.writer is pointing to Members.UserLogin

I want to display with as little code as possible, the whole list of registered UserLogins but also a count of how many articles they might have written, or if they have´nt written any they should still be on the list as well.

What I would like: [UserLogin | Name | ArticlesWritten]

What I´ve got so far is:

SELECT UserLogin, Name, Writer, count(*)
FROM Article
INNER JOIN Members on Writer=UserLogin
GROUP BY UserLogin;

or

SELECT UserLogin, Name, count(Writer)
FROM Article
LEFT JOIN Members ON UserLogin = Writer
GROUP BY 1;

Both of them lists everything fine in mysql, but none of them contains the UserLogins and Names of those who never has written any article. Can you guys point me in the right direction. I Understand the problem with my queries, but I have no idea on how to solve it.

I´ve found similar problems on this forum, but I have´nt got to any solution from them. Might be a language barriere or just a plain lack of basic mysql-knowledge.

Answer 1

You can use subquery

SELECT m.UserLogin, 
       m.Name, 
       (SELECT COUNT(*) 
          FROM Articles 
         WHERE Writer = m.UserLogin) ArticlesWritten
  FROM Members m

Sample output:

| USERLOGIN |  NAME | ARTICLESWRITTEN |
---------------------------------------
|     user1 |  Jhon |               2 |
|     user2 | Helen |               0 |

Here is SQLFiddle

Answer 2

just use LEFT JOIN an interchange the table names,

SELECT UserLogin, Name, count(Writer)
FROM   Members 
       LEFT JOIN Article on Writer = UserLogin
GROUP  BY UserLogin, Name

To further gain more knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

in your question you state, "foreign key is "Name" which is pointing to "Writer", isn't it writer is pointing to UserLogin in which table Article is dependent on table Members?

Answer 3

SELECT
  m.UserLogin,
  m.Name,
  (SELECT
     COUNT(a.ArtID)
   FROM Article a
   WHERE a.Writer = m.UserLogin) AS ArticlesWritten
FROM Members m