Databases
StackOverflow2013
Postsdbo
POSQL Query with multiple possible joins (or condition in join)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Query with multiple possible joins (or condition in join)
    primarykey
    data
    text
    <p>I have a problem where I have to try to find people who have old accounts with an outstanding balance, but who have created a new account. I need to match them by comparing SSNs. The problem is that we have primary and additional contacts, so 2 potential SSNs per account. I need to match it even if they where primary at first, but now are secondary etc.</p> <p>Here was my first attempt, I'm just counting now to get the joins and conditions down. I'll select actual data later. Basically the personal table is joined once to active accounts, and another copy to delinquent accounts. The two references to the personal table are then compared based on the 4 possible ways SSNs could be related.</p> <pre><code>select count(*) from personal pa join consumer c on c.cust_nbr = pa.cust_nbr and c.per_acct = pa.acct join personal pu on pu.ssn = pa.ssn or pu.ssn = pa.addl_ssn or pu.addl_ssn = pa.ssn or pu.addl_ssn = pa.addl_ssn join uncol_acct u on u.cust_nbr = pu.cust_nbr and u.per_acct = pu.acct where u.curr_bal &gt; 0 </code></pre> <p>This works, but it takes 20 minutes to run. I found this question <a href="https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea">Is having an &#39;OR&#39; in an INNER JOIN condition a bad idea?</a> so I tried re-writing it as 4 queries (one per ssn combination) and unioning them. This took 30 minutes to run.</p> <p>Is there a better way to do this, or is it just a really inefficient process no mater how you do it?</p> <p><strong>Update:</strong> After playing with some options here, and some other experimenting I think I found the problem. Our software vendor encrypts the SSNs in the database and provides a view that decrypts them. Since I have to work from that view it takes a really long time to decrypt and then compare.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USMike
    plurals
    1. PL
      singulars
      1. LTLinked
    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. POSQL Query with multiple possible joins (or condition in join)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Query with multiple possible joins (or condition in join)
      1. USuser75ponic
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Query with multiple possible joins (or condition in join)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow are the tables indexed? You might get a significant speed-up if you index by ssn and addl_ssn (separate indexes for each)
      singulars
      1. POSQL Query with multiple possible joins (or condition in join)
      1. USSWeko
    2. COThe database is managed by our software vendor. I'm not sure how indexing is setup or if I could have them change that. I could check into that.
      singulars
      1. POSQL Query with multiple possible joins (or condition in join)
      1. USMike
 

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