Databases
StackOverflow2013
Postsdbo
PORetrieve COUNT() even without data returned
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORetrieve COUNT() even without data returned
    primarykey
    data
    text
    <p>I am trying to retrieve a few things from the database with just one single query: - the number of friend requests - the number of new private messages - the number of notifications - and username of the user who eg. sent the PM to you or invited you to friends.</p> <p>The user id specified below in the query is the user who is checking to see if there are any notifications for this user.</p> <p>The problem is: if there are no notifications it will not return the number of friend requests or private messages as well.</p> <p>This is the query I have build, and it works apart from the problem above:</p> <pre><code>SELECT n.id, n.type, u.username, COUNT(fr.friend_id) as frc, COUNT(pm.pm_id) as pmc FROM notifications as n, users as u LEFT JOIN private_messages as pm ON pm.to_user = '1' AND pm.status = 'unread' LEFT JOIN friends as fr ON fr.friend_id = '1' AND fr.status = 'pending' WHERE n.user_id='1' AND u.id = n.from_id ORDER BY n.id ASC </code></pre> <p>I am not sure what I am missing, so if anyone can share their sql knowledge and help me out I would deeply appreciate it.</p> <p>Thanks!</p> <p>Ps. I am still very new to more complex queries, so if I am completely off track with my above query, please do let me know :).</p> <p>Table Structures:</p> <pre><code>Sample friends table structure +--------------------------+-----------------------------+ | Field | Type | +--------------------------+-----------------------------+ | user_id | int(10) | | friend_id | int(10) | | status | ENUM('pending','accepted') | | user_id | int(10) | +--------------------------+-----------------------------+ Sample notifications table structure +--------------------------+---------------------+ | Field | Type | +--------------------------+---------------------+ | id | int(10) | | type | ENUM('pm','friend') | | user_id | int(10) | | from_id | int(10) | +--------------------------+---------------------+ Sample private message table structure +--------------------------+-----------------------+ | Field | Type | +--------------------------+-----------------------+ | pm_id | int(12) | | to_user | int(10) | | from_user | int(10) | | status | ENUM('read','unread') | +--------------------------+-----------------------+ Sample users table structure +--------------------------+---------------+ | Field | Type | +--------------------------+---------------+ | id | int(10) | | username | varchar(50) | +--------------------------+---------------+ </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMrE
    1. USMrE
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you tried "UNION SELECT" ?
      singulars
      1. PORetrieve COUNT() even without data returned
      1. USStephen Quan
    2. COI have no idea what it is, so can't say I have no. :(
      singulars
      1. PORetrieve COUNT() even without data returned
      1. USMrE
    3. COIf you post the table structure it could help (not necessarily all the columns, just whatever you're using in the query)
      singulars
      1. PORetrieve COUNT() even without data returned
      1. USIan Jacobs
 

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