Join Query Not Working

Question

Hello,

I am using three MySQl tables:

comment

commentid loginid submissionid comment datecommented

login

loginid username password email actcode disabled activated created points

submission

submissionid loginid title url displayurl datesubmitted

In these three tables, the "loginid" correspond.

I would like to pull the top 10 loginids based on the number of "submissionid"s. I would like to display them in a 3-column HTML table that shows the "username" in the first column, the number of "submissionid"s in the second column, and the number of "commentid"s in the third column.

I tried using the query below but it did not work. Any idea why not?

Thanks in advance,

John

$sqlStr = "SELECT
                 l.username 
                 ,l.loginid  
                 ,c.commentid 
                 ,count(s.commentid) countComments
                 ,c.comment 
                 ,c.datecommented 
                 ,s.submissionid 
                 ,count(s.submissionid) countSubmissions
                 ,s.title
                 ,s.url 
                 ,s.displayurl 
                 ,s.datesubmitted
            FROM comment AS c
      INNER JOIN login AS l ON c.loginid = l.loginid
      INNER JOIN submission AS s ON c.loginid = s.loginid
        GROUP BY c.loginid
        ORDER BY countSubmissions DESC
           LIMIT 10";

  $result = mysql_query($sqlStr);

$arr = array(); 
echo "<table class=\"samplesrec1\">";
while ($row = mysql_fetch_array($result)) { 
    echo '<tr>';
    echo '<td class="sitename1"><a href="http://www...com/.../members/index.php?profile='.$row["username"].'"&gt;'.stripslashes($row["username"]).'&lt;/a&gt;&lt;/td&gt;';
    echo '</tr>';
    echo '<td class="sitename1">'.stripslashes($row["countSubmissions"]).'</td>';
    echo '</tr>';
    echo '</tr>';
    echo '<td class="sitename1">'.stripslashes($row["countComments"]).'</td>';
    echo '</tr>';
    }
echo "</table>";

Answer 1

select
 l.username,
 s.submissions,
 c.comments
from
 l,
 (
  select
   count(s.submissionid) as submissions,
   s.loginid
  from
   submission s
  group by
   s.loginid
 ) s,
 (
  select
   count(c.commentid) as commentids,
   c.loginid
  from
   comment c
  group by
   c.loginid
 ) c
where
 l.loginid = s.loginid and
 l.loginid = c.loginid
order by
 s.submissions desc
limit
 10

Answer 2

SELECT 
    l.loginid, 
    l.username, 
    COALESCE(s.total, 0) AS numSubmissions, 
    COALESCE(c.total, 0) AS numComments
FROM login l    
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM submission 
    GROUP BY loginid
) s ON l.loginid = s.loginid
LEFT JOIN (
    SELECT loginid, COUNT(1) AS total 
    FROM comment 
    GROUP BY loginid
) c ON l.loginid = c.loginid
GROUP BY l.loginid
ORDER BY numComments DESC

Answer 3

In your query , you have selected non-group items such as commentid , comment etc. This should give the desired result.

select l.username , count(s.submissionid) as NoOfSubmissions,count(c.commentid) as NoOfComments from comment c INNER JOIN submission s ON c.submissionid = s.submissionid INNER JOIN login l ON l.loginid = c.loginid group by l.username order by count(s.submissionid) limit 10;

Thanks,

Rinson KE DBA 91 + 9995044142 www.qburst.com