Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This works for your data:</p> <pre><code>select a.* , children = isnull(c.children, 0) from accounts a outer apply (select children = count(1) from accounts c where a.option1 is null and a.id = c.id and a.id2 = c.id2 and a.id3 = c.id3 and a.custId = c.custId and c.option2 &lt;&gt; 0) c where (a.option1 = 'solo' or a.option2 = 0) and a.type = 'foo' </code></pre> <p><a href="http://sqlfiddle.com/#!3/a0579/2" rel="nofollow">SQL Fiddle with demo</a>.</p> <p><strong>Edit after comment:</strong></p> <p>OK, so the main part of the query is the <code>WHERE</code> clause - this determines the three rows that are returned.</p> <p>Once we have these rows, we need to work out how many children there are - this is what the <code>OUTER APPLY</code> achieves.</p> <p>Basically, for each non-solo row, we match this to any child rows (i.e. <code>c.option2 &lt;&gt; 0</code>) and get a count of these rows, explicitly returning 0 when there are now matches or it is a <strong>'solo'</strong> parent row.</p> <p>Since we are only matching against non-solo parent rows, we filter these with <code>a.option1 is null</code>, i.e. checking the parent row <code>option1</code> values before checking for any matches. Due to the nature of the data, <code>c.option1 is null</code> would be fine, too, as both parent and child non-solo rows have <code>null</code> <code>option1</code> values.</p> <p><code>count(1)</code> doesn't mean anything in particular; it's just an arbitrary value to be counted for each row, in this case a constant. You could just as easily use <code>count(*)</code>. Back in the day this might have made a difference in query processing but with modern RDBMS optimisers you won't see any differences.</p> <p>Here's an alternate query that does it a slightly different way:</p> <pre><code>select a.* , children = isnull(c.children, 0) from accounts a left join (select children = count(*) , id , id2 , id3 , custId from accounts where option1 is null and option2 &lt;&gt; 0 group by id , id2 , id3 , custId) c on a.id = c.id and a.id2 = c.id2 and a.id3 = c.id3 and a.custId = c.custId where (a.option1 = 'solo' or a.option2 = 0) and a.type = 'foo' </code></pre> <p><a href="http://sqlfiddle.com/#!3/a0579/3" rel="nofollow">SQL Fiddle with demo</a>.</p> <p>So it uses <code>count(*)</code> and a <code>LEFT JOIN</code> instead of <code>count(1)</code> and <code>OUTER APPLY</code> respectively. <code>OUTER APPLY</code> gets applied to every row outside the query; you can use it for functions, but it can help code be more concise, too, like in this case. Just personal preference for me making these choices.</p>
    singulars
    1. This table or related slice is empty.
    1. POhaving a count column from a subquery that's querying the same table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USIan Preston
    1. USIan Preston
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POhaving a count column from a subquery that's querying the same table
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you, this worked well. Could you explain what everything does? I know it checks if it gets a null result and puts 0 children on it, but what does count(1) do? I will mark it as answer, but if it's not too much to ask some documentation would be nice :) Thanks again.
      singulars
      1. PO
      1. USNimchip
    2. COalso i'm assuming it should be `c.option1 is null` instead
      singulars
      1. PO
      1. USNimchip
    3. COHi, @Nimchip, I've tried to document everything, but describing code is harder than writing it! Anyway, I'm glad the query works and I hope the extra detail is helpful. I also corrected a minor typo - whoops! Make sure you are basing your code on these latest versions.
      singulars
      1. PO
      1. USIan Preston
 

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