Databases
StackOverflow2013
Postsdbo
POCounting number of joined rows in left join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCounting number of joined rows in left join
    primarykey
    data
    text
    <p>I'm trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should be <code>0</code>:</p> <h2>Data</h2> <p>My database looks like this (I'm not able to change the structure, unfortunately):</p> <pre class="lang-none prettyprint-override"><code>MESSAGE ---------------------------------------------- MESSAGEID SENDER SUBJECT ---------------------------------------------- 1 Tim Rabbit of Caerbannog 2 Bridgekeeper Bridge of Death MESSAGEPART ---------------------------------------------- MESSAGEID PARTNO CONTENT ---------------------------------------------- 1 0 (BLOB) 1 1 (BLOB) 3 0 (BLOB) </code></pre> <p>(<code>MESSAGEPART</code> has a composite <code>PRIMARY KEY("MESSAGEID", "PARTNO")</code>)</p> <h2>Desired output</h2> <p>Given the data above I should get something like this:</p> <pre class="lang-none prettyprint-override"><code>MESSAGEID COUNT(*) ----------------------------------------------- 1 2 2 0 </code></pre> <p>It seems obvious that I need to do a left join on the <code>MESSAGE</code> table, but how do I return a count of <code>0</code> for rows where the joined columns from <code>MESSAGEPART</code> are <code>NULL</code>? I've tried the following:</p> <h2>Logic</h2> <p>I've tried</p> <pre><code>SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY m.MESSAGEID; </code></pre> <p>However, this returns</p> <pre class="lang-none prettyprint-override"><code>MESSAGEID COUNT(*) ----------------------------------------------- 1 2 2 1 </code></pre> <p>I've also tried</p> <pre><code>SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID GROUP BY mp.MESSAGEID; </code></pre> <p>but this returns</p> <pre class="lang-none prettyprint-override"><code>MESSAGEID COUNT(*) ----------------------------------------------- 1 2 1 </code></pre> <p>What am I doing wrong here?</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. USerrantlinguist
    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
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COFor a start, use `m.MESSAGEID` (will always be there) rather than `mp.MESSAGEID` (will only be there when there's a match).
      singulars
      1. POCounting number of joined rows in left join
      1. USAnthony Grist
 

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