Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your basic problem is that you're trying to roll up to two different record sets.<br> The initial set (the <code>SELECT</code> and <code>GROUP BY</code> clauses) is saying that you want one record for every difference in the set [ProfileId, SystemId, EntitlementId, TargetId, fConnector].<br> The second set (the <code>HAVING</code> clause) is saying that you want, for <em>every row in the inital set</em>, to compare it's <code>COUNT</code> of records with the <code>SUM</code> of the connections. However, because you've asked for grouping down to the individual flag, this has the effect of getting a <em>single row</em> for <em>each</em> flag (assuming 1-to-1 relationships). Effectively, you're saying - 'Hey, if this target has a connection? Yeah, I want it'. </p> <p>What you appear to <em>want</em> is a roll up to the <code>SystemId</code> value. To do that, you will need to change your <code>SELECT</code> and <code>GROUP BY</code> clauses to only include the set [ProfileId, SystemId]. This will return only those rows (keyed from profile and system) who has all targets 'connected'. You will <em>not</em> be able to see the individual entitlements, targets, and whether they are connected (you will be able to <em>infer</em> that they will all be/not be connected, however).</p> <hr/> <p>EDIT: </p> <p>In the interests of full disclosure, here is how you'd get something similar to your original results set, where it lists all <code>EntitlementId</code>s and <code>TargetId</code>s:</p> <pre><code>WITH all_connections as (SELECT pset.ProfileId, pset.SystemRoleId FROM @pset pset INNER JOIN vuTargets vt ON vt.TargetId = pset.TargetId INNER JOIN @conn conn ON conn.TargetName = vt.TargetName GROUP BY pset.ProfileId, pset.SystemRoleId HAVING COUNT(pset.SystemRoleId) = SUM(CAST(fConnector as INT))) SELECT pset.* FROM @pset pset JOIN all_connections conn ON conn.ProfileId = pset.ProfileId AND conn.SystemRoleId = pset.SystemRoleId </code></pre> <p>This should get you a listing, down to the <code>TargetId</code>, of <code>ProfileId</code>/<code>SystemRoleId</code> keys where all <code>EntitlementId</code>s and <code>TargetId</code>s have a connection (or, flip the CTE <code>=</code> to <code>&lt;&gt;</code> for those where not all do).</p>
    singulars
    1. This table or related slice is empty.
    1. POUsing Count() and Sum() correctly in SQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USClockwork-Muse
    1. USClockwork-Muse
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUsing Count() and Sum() correctly in SQL?
      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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYes, you've explained it better than I could. I will give this a shot, hope it works!
      singulars
      1. PO
      1. USTommy Fisk
    2. COWorked like a charm, thanks.
      singulars
      1. PO
      1. USTommy Fisk
    3. CO@TommyFisk - I've added a query that will give you results similar in _display_ to your results set, but only displaying if your original criteria is matched.
      singulars
      1. PO
      1. USClockwork-Muse
 

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