Databases
StackOverflow2013
Postsdbo
POhaving a count column from a subquery that's querying the same table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhaving a count column from a subquery that's querying the same table
    primarykey
    data
    text
    <p>Lets say i have the table:</p> <p><strong>accounts</strong>: id, id2, id3, custId, option1, option2, type</p> <p>and this same table has ALL accounts with some being "parent" accounts (which can be "solo" accounts with no children) and some being "children" accounts (bad design i know, but that's what I'm working with). </p> <p>The distinct/composite key for each account is <code>id + id2 + id3 + option1 + option2 + custId</code>.</p> <p>I want to query a list of "parent" or "solo" accounts with a specific <code>custId</code> and <code>type</code>, which is easily done by:</p> <pre><code>Select * From accounts Where custId = 1 And type = 'foo' and (option1 = 'solo' Or option2 = 0) </code></pre> <p>where <code>'solo'</code> means it's a solo account and has no children and <code>0</code> means that's its the first of a line of accounts and therefore its parent.</p> <p>Then I want to obtain the count of "children" associated to every parent obtained by the result set of the above query. Obviously "solo" accounts won't have children.</p> <p>For example obtaining the "children" count from a specific "parent" account would be something like (let's say I'm looking for the children of account with id=1, id2=1, id3=1:</p> <pre><code>Select Count(*) From accounts Where id = 1 And id2 = 1 And id3 = 1 And custId = 1 And option1 != 'solo' And option2 != 0) </code></pre> <p>So how can I combine the two queries in order to obtain the result set of the first with counts for each of its rows?</p> <p><strong>Example</strong></p> <p>populating the table we could have:</p> <pre><code> id id2 id3 custId option1 option2 type ------------------------------------------------------------ 1 1 1 1 solo 9 foo 2 2 2 1 solo 9 foo 3 4 4 1 NULL 0 foo 3 4 4 1 NULL 1 foo 3 4 4 1 NULL 2 foo </code></pre> <p>I want a result set like this:</p> <pre><code> id id2 id3 custId option1 option2 type children ------------------------------------------------------------------------- 1 1 1 1 solo 9 foo 0 2 2 2 1 solo 9 foo 0 3 4 4 1 NULL 0 foo 2 </code></pre> <p>Basically I would want something like this (i know this is wrong)</p> <pre><code>Select *, (Select count(*) from accounts Where option1 != 'solo' And option2 != 0 And --all 3 ids and custId equal to the current row ) --this is the part i don't know how to do From accounts Where custId = 1 And Type = 'foo' And (option1 = 'solo' Or option2 = 0) </code></pre> <p>My brain is running circles around how to do this. Thank you for your help. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNimchip
    1. USNimchip
    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
    1. VO
      singulars
      1. POhaving a count column from a subquery that's querying the same table
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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