ArdaTahsinAyar ArdaTahsinAyar - 11 months ago 41
SQL Question

Get One Normal Table and Counts From Another Table In One SQL Query (JOIN)

I need a SQL query which gives content of one table and count of second table. I tried, could not.

I have a "comments" table. And in the another table named "likings", there are numbers of likes of comments.

In my algorithm, in the "likings" table, there are a column named "

likeType
", if it is "
1
" that means a like, otherwise (if it is "
0
") means a dislike. I need to bring comment from "
comments
" table, counts of
likeType=1
and
likeType=0
from "
likings
" table in one query.

Here is my best attempt for that, which didn't work:




PHP side:



$getFirst8Comments = "SELECT
episodecomments.cmtID,
episodecomments.cmtConEpisode,
episodecomments.cmtOwner,
episodecomments.cmtDate,
episodecomments.cmtContent,
episodecomments.cmtSpoiler,
SUM(IF(episodecommentsliking.liType='1', 1, 0)) AS likes,
SUM(IF(episodecommentsliking.liType='0', 1, 0)) AS dislikes
FROM episodecomments
LEFT JOIN episodecommentsliking
ON episodecomments.cmtID = episodecommentsliking.liCmtID
GROUP BY
episodecomments.cmtID,
episodecomments.cmtConEpisode,
episodecomments.cmtOwner,
episodecomments.cmtDate,
episodecomments.cmtContent,
episodecomments.cmtSpoiler
WHERE episodecomments.cmtConEpisode='$epID'
ORDER BY episodecomments.cmtID
DESC LIMIT 8";

while ($getF8C = mysqli_fetch_array($getFirst8Comments))
{
echo "Something coming through-<br>";
}




Error pops out:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, string given in J:\file.php on line 3484 (error in variable $getFirst8Comments)


Thanks in advance.

Answer Source

MySQL doesn't support COUNT in that fashion. You'll also need to inform it what to GROUP BY for those aggregate (sum) functions.

Try using SUM instead: (COUNT would work if you're counting NULL vs non-NULL values however - see the MySQL documentation)

  SELECT
    comments.cmtID,
    comments.cmtConEpisode,
    comments.cmtOwner,
    comments.cmtDate,
    comments.cmtContent,
    comments.cmtSpoiler,
    SUM(IF(commentsliking.liType='1', 1, 0)) AS likes,
    SUM(IF(commentsliking.liType='0', 1, 0)) AS dislikes
  FROM comments
  INNER JOIN commentsliking
    ON comments.cmtID = commentsliking.liCmtID
  GROUP BY
    comments.cmtID,
    comments.cmtConEpisode,
    comments.cmtOwner,
    comments.cmtDate,
    comments.cmtContent,
    comments.cmtSpoiler
  ORDER BY comments.cmtID
  DESC LIMIT 8

Also - use "LEFT JOIN" if there's the possibility that a comment record will exist with no like data.


I've further updated my answer to address the error you've since reported being thrown by the mysqli_* commands.

Check the PHP documentation for full details on how to correctly use the mysqli_fetch_array() function - especially with regards to it requiring a mysqli_result type as an argument, as opposed to a string [of SQL])