Databases
StackOverflow2013
Postsdbo
POHow can I find groups of records that match other groups of records (relational division?)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I find groups of records that match other groups of records (relational division?)
    primarykey
    data
    text
    <p>For setting consolidated account handling, I want to find out accounts that have "exactly the same" set of owners.</p> <p>I think it might work to pivot the owners with dynamic sql, then use ranking functions, but I don't want to pursue that approach; I don't have an upper limit on how many names can be associated with a given account, so I want to avoid dynamic SQL.</p> <p>My data (also this is at <a href="http://www.sqlfiddle.com/#!3/1d36e" rel="nofollow">http://www.sqlfiddle.com/#!3/1d36e</a>)</p> <pre><code> CREATE TABLE allacctRels (account INT NOT NULL, module CHAR(3) NOT NULL, custCode CHAR(20) NOT NULL) INSERT INTO allacctrels (account, module, custCode) VALUES (1, 'DDA', 'Wilkie, Walker'), (1, 'DDA', 'Houzemeal, Juvy'), (2, 'CDS', 'Chase, Billy'), (2, 'CDS', 'Norman, Storm'), (3, 'CDS', 'Chase, Billy'), (3, 'CDS', 'Norman, Storm'), (7, 'CDS', 'Perkins, Tony'), (15, 'SVG', 'Wilkie, Walker'), --typo in name before mwigdahl's response (16, 'SVG', 'Wilkie, Walker'), -- corrected typo here too (606, 'DDA', 'Norman, Storm'), (606, 'DDA', 'Chase, Billy'),-- corrected 2nd typo found (4, 'LNS', 'Wilkie, Walker'), (4, 'LNS', 'Houzemeal, Juvy'), (44, 'DDA', 'Perkins, Tony'), (222, 'DDA', 'Wilkie, Walker'), (222, 'DDA', 'Houzemeal, Juvy'), (17, 'SVG', 'Wilkie, Walker'), -- added these three rows in edit, SVG 17 doesn't match any dda (17, 'SVG', 'Welch, Raquel'), (17, 'SVG', 'Houzemeal, Juvy') </code></pre> <p>I want to find out, for each MODULE-ACCOUNT, what the lowest DDA account is that has the exact same owners associated with it. </p> <p>In the sample data, I would want these results, the third column being the lowest DDA account that has the same owners. The results should have the same number of rows as therea re module/account combos - one row per each row in "SELECT DISTINCT module, account FROM allAcctRels")</p> <pre><code>1, DDA, 1 2, CDS, 606 3, CDS, 606 15, SVG, NULL 16, SVG, NULL 606, DDA, 606 4, LNS, 1 7, CDS, 44 44, DDA, 44 222, DDA, 1 17, SVG, NULL -- added to original post. </code></pre> <p>SVG 15 and 16 don't match any DDA account, so it doesn't matter that they match each other, they get NULL for the account to consolidate to. <em>EDIT: SVG 17 doesn't match anything, even though there is a DDA acct that has all its holders on in SVG 17, the combination of holders in SVG 17 doesn't occur for any one DDA acct.</em> Every DDA account will match itself, unless a dda account with the same owners and lower DDA exists (as is the case for DDA 222).</p> <p>I can see that one general approach is to pivot each account, group the pivoted table, and use row_number. Given the unbounded number of holders associated with each account, I think the pivoting would take dynamic SQL I'd rather avoid.</p> <p>It seems to me that this is a "relational divsion" problem, with the relational division probably being "fed" by a CROSS APPLY. I tried writing a function that would take a table of account holders associated with a specific account and find the lowest dda account, along the lines shown below, the idea being to see if all the the number of people in a given account is the same as the number of people when that account is joined to a given dda account, but I can't figure out how to "feed" tables of account numbers in to the function.</p> <pre><code>-- this is what I tried but I'm not sure it the logic would work -- and I can't figure out how to pass the account holders for each -- account in. This is a bit changed from the function I wrote, some -- extraneous fields removed and cryptic column names changed. So it -- probably won't run as is. -- to support a parameter type to a tape -- CREATE type VisionCustomer as Table -- (customer varchar(30)) CREATE FUNCTION consolidatable (@custList dbo.VisionCustomer READONLY) RETURNS char(10) AS BEGIN DECLARE @retval Varchar(10) DECLARE @howmany int select @howmany=Count(*) FROM @custlist; SELECT @retval = min (acct) FROM allAcctRels JOIN @custlist On VendorCustNo = Customer WHERE acctType = 'DDA' GROUP BY acct HAVING (count(*) = @howmany) and COUNT(*) = (select Count(*) FROM allAcctRels X WHERE X.acctType = 'DDA' AND X.account = AllAcctRels.account) ; RETURN @retval END; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLevin Magruder
    1. USLevin Magruder
    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
    1. VO
      singulars
      1. POHow can I find groups of records that match other groups of records (relational division?)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow can I find groups of records that match other groups of records (relational division?)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I find groups of records that match other groups of records (relational division?)
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONote that "Chase, Billie" in the 606 DDA row doesn't jibe with the resultset you say you want back; I think you want this to be "Chase, Billy", right?
      singulars
      1. POHow can I find groups of records that match other groups of records (relational division?)
      1. USmwigdahl
    2. COYes, that is correct, sorry about that, and thank you, I'll re-edit now
      singulars
      1. POHow can I find groups of records that match other groups of records (relational division?)
      1. USLevin Magruder
 

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