Databases
StackOverflow2013
Postsdbo
PORow counting a JOINed column without affecting the returned rows?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORow counting a JOINed column without affecting the returned rows?
    text
    copied!<p>It's a bit difficult getting my problem into a short form, so I apologise if the title doesn't make sense.</p> <p>Anyway, here is the problem:</p> <pre><code> $query = ' SELECT issues.*, comments.author AS commentauthor, favorites.userid AS favorited FROM issues LEFT JOIN comments ON comments.issue = issues.id AND comments.when_posted = issues.when_updated LEFT JOIN favorites ON favorites.ticketid = issues.id AND favorites.userid = \'' . $_SESSION['uid'] . '\' ' . $whereclause . ' ORDER BY issues.when_updated ' . $order; </code></pre> <p>Don't mind the fact that it's PHP as I am <strong>not</strong> asking for PHP help.</p> <p>The query retrieves a bunch of <code>issues</code>, and what I'm wishing to do is obtain the row count of <code>favorites</code> that have <code>favorites.ticketid</code> matching <code>issues.id</code>. My use of <code>LEFT JOIN favorites</code> is not to get what I've just mentioned, but instead to obtain whether the client has favourited the issue, thus the part <code>favorites.userid AS favorited</code>.</p> <p>I have tried doing the following: (all at once, I'm putting this in bulleted form for readibility)</p> <ul> <li>duplicating the existing <code>LEFT JOIN favorites</code> and removing the user id check from the duplicate</li> <li>adding <code>, COUNT(favorites.ticketid) AS favoritescount</code> to the <code>SELECT</code> section</li> <li>adding <code>AS favorited</code> to the original <code>LEFT JOIN</code> as well as changing <code>favorites.userid</code> to <code>favorited.userid</code></li> </ul> <p>With that attempt, my query ends up returning only one row.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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