Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I suggest two approaches. One is similar to your own, but removing the subquery and correcting the <code>COUNT</code> clause. I should mention it is unclear from your question whether the <code>COUNT ... AS users</code> result is of importance here, since you only specified you want to get all folders for a user.</p> <pre><code>SELECT folders.*, COUNT(DISTINCT IFNULL(userGroupRelation.userID, folders.ownerID)) AS users FROM folders LEFT JOIN userGroupRelation ON folders.postingGroupID = userGroupRelation.userGroupID WHERE folders.ownerID = 1 OR userGroupRelation.userID = 1 GROUP BY folders.folderID ; </code></pre> <p>This is somewhat better than your own solution because the subquery is eliminated, but it still leaves with this <code>OR</code> condition which is difficult to optimize -- it works on two different tables.</p> <p><strong>Edit</strong>: this next solution neither answers the question nor does it work at all :(</p> <p>A completely different approach is to start with the <code>users</code> table (assuming you have one, of course):</p> <pre><code>SELECT folders.* FROM users LEFT JOIN folders ON (folders.ownerID = users.id) LEFT JOIN userGroupRelation ON (userGroupRelation.userId = users.id) LEFT JOIN folders ON (folders.postingGroupID = userGroupRelation.userGroupID) WHERE users.id = 1 GROUP BY folders.folderID ; </code></pre> <p>This one is far better optimized if you have proper indexes on <code>users.id</code> and all joining columns. However:</p> <ol> <li>It will not tell you the amount of (other) users per folder</li> <li><code>SELECT filders.* .... GROUP BY filders.folderId</code> is not strictly valid SQL -- though it will work if you're not using strict <code>sql_mode</code>. From you own query I can see you're not, so I won't dwell into that.</li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POAmount of users in a group where one particular user is present mysql
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USShlomi Noach
    1. USShlomi Noach
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COThank you for your response. I'm sorry I didn't clarify, but I do need to know if I have other posters in the folders, and that is why I added the IN (because if not, the owner of the folder would not see the real amount of other posters for it.
      singulars
      1. PO
      1. USManatax
    2. CONot a unique table/alias...
      singulars
      1. PO
      1. USManatax
    3. COI see, Then please ignore second offered solution. You are also right about the "not a unique table/alias" -- I did not have schema/data to work on -- therefore could not verify the query. Not only is there an ambiguity -- it could also not be easily solved. The second offered solution is simply incorrect.
      singulars
      1. PO
      1. USShlomi Noach
 

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