Databases
StackOverflow2013
Postsdbo
POMySQL COUNT function not performing as I would like in multiple joined query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL COUNT function not performing as I would like in multiple joined query
    primarykey
    data
    text
    <p>I have been altering a query for a while now, reading many posts on this wonderful site to get to where I am with this so far. But, alas, now I am stuck :(</p> <p>This query and relevant part of the database I have designed is similar to the youtube comment liking system. Relevant tables and fields are:</p> <p>USRS<br /> usr_id int (PK)</p> <p>COMMENTS<br /> comment_id int (PK) usr_id int (FK) references usrs(usr_id) topic_id int (FK) references topics(topic_id) descr varchar created varchar</p> <p>COMMENT_LIKERS<br /> comment_id int (PK) (FK) references comments(comment_id) usr_id int (PK) (FK) references usrs(usr_id) liker tinyint</p> <p>I want to be able to select all relevant data in one query. Aside from general data for each comment, I want to count all likes and dislikes for each comment. The query I have thus far is counting the likes for ALL comments and not for each even though I have the LEFT JOIN with ON clause: comments.comment_id = comment_likers.comment_id.</p> <p>I am learning MySql and PHP so go easy with me if I have done something silly. I assure you I have looked all around for clues to the answer.</p> <p>Here is the query:</p> <pre><code>SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name, COUNT(if(comment_likers.liker = 1, 1, null)), COUNT(if(comment_likers.liker = 0, 1, null)), comment_likers2.liker FROM comments INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id ) LEFT JOIN comment_likers ON ( comments.comment_id = comment_likers.comment_id ) LEFT JOIN comment_likers AS comment_likers2 ON ( comments.comment_id = comment_likers.comment_id AND comment_likers.usr_id = $usrID ) WHERE comments.topic_id = $tpcID GROUP BY comments.comment_id ORDER BY comments.created DESC </code></pre> <p>Thanks in advance</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USPsyrus
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL COUNT function not performing as I would like in multiple joined query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL COUNT function not performing as I would like in multiple joined query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't think it is your problem, but I would put " AND comment_likers.usr_id = $usrID" in the where clause instead of the join.
      singulars
      1. POMySQL COUNT function not performing as I would like in multiple joined query
      1. USJohnie Karr
    2. COI don't know if I am going mad here, having stared at this code for too long... but I know what you mean, I have switched over similar AND's in other queries to the WHERE clause with no apparent difference but I just tried doing so with this one and the query doesn't even work... no comments are found
      singulars
      1. POMySQL COUNT function not performing as I would like in multiple joined query
      1. USPsyrus
    3. COI should have added this link in my comment, that I stumbled upon in my quest for answers in this project: http://techstumbler.blogspot.com/2008/04/mysql-join-with-count.html I will go to bed now and hope for answers and a fresh head tomorrow
      singulars
      1. POMySQL COUNT function not performing as I would like in multiple joined query
      1. USPsyrus
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload