CODEWITHSUNDEEP
sqlsql-servert-sql
Brad H_KC
Brad H_KC

Reputation: 71

Difference between tables where 1 to 3 relationship on key field

I am trying to create a table using SQL server that creates a table of differences between the two. The tricky part about this that I haven't found in other posts is that one table is single "Account" data like below:

TABLE A      
Account  Security ID  Sec Name   Shares
-------  -----------  ---------  ------
1        Sec1         Security1  20000
1        Sec2         Security2  50000
1        Sec3         Security3  10000
1        Sec4         Security4  35000

and the data that I want to compare this to is a group of "Accounts" (3 in this example):

TABLE B        
Parent_acct  Account  Security ID  Sec Name   Shares
-----------  -------  -----------  ---------  ------
Clone        200      Sec1         Security1  15000
Clone        200      Sec3         Security3  22000
Clone        200      Sec4         Security4  8000
Clone        300      Sec1         Security1  11000
Clone        300      Sec3         Security3  8500
Clone        300      Sec4         Security4  11200
Clone        400      Sec1         Security1  16000
Clone        400      Sec2         Security2  7800
Clone        400      Sec3         Security3  3500

I need some sql to locate security ID's included in Table A that are missing from Table B for each account

For instance, given the two tables above, I would expect the output below that tells me the security that is missing and what account it is missing from.

Hopefully, that makes sense.

I have used a cursor query to generate the data I need, however, I’m trying to create something that I can easily apply to Crystal Reports and a cursor query is not useful for that.

Thanks in advance for taking a look at this.

Output    
Account   Security ID  Sec Name
(From B)  (From A)     (From A)
-------   -----------  ---------
200       Sec2         Security2
300       Sec2         Security2
400       Sec4         Security4

My query so far:

select * FROM
(SELECT * from 
(select p.acct_cd, s.ext_sec_id, s.sec_name, p.qty_sod
from csm_Security s, cs_position p
where s.sec_id = p.sec_id
and p.acct_cd = '329'
and s.sec_typ_cd in ('COM','FSTK','ADR')) A
cross join 
(select distinct child_acct_cd
cs_fund_config fc
where fc.parent_acct_cd IN ('clone_as')) B) AAccounts,
(select fc.child_acct_cd, s.ext_sec_id, s.sec_name, p.qty_sod
from csm_Security s, cs_position p, cs_fund_config fc
where s.sec_id = p.sec_id
and fc.child_acct_cd = p.acct_cd
and fc.parent_acct_cd IN ('clone_as')
and s.sec_typ_cd in ('COM','FSTK','ADR')) BAccounts
where AAccounts.ext_sec_id *= BAccounts.ext_sec_id
and AAccounts.child_acct_cd *= BAccounts.child_acct_cd

Upvotes: 2

Views: 116

Answers (2)

Segfault
Segfault

Reputation: 8290

As far as I can tell, this should be a relatively straight forward outer join. It's called an Outer Join because we want to include the rows from one table that don't have a match in the second table as well as the ones that do. Use the keywords LEFT JOIN because we put table A on the left of the join clause, this indicates we are doing an outer join and the rows from A that don't have a match in B should be included. The where clause will select only those rows. You won't be able to get the Account number from B with this of course, because there is no row in table B for this Security Id. The Account number from A should be good though, right?

SELECT
  A.Account,
  A.[Security Id]
  A.[Sec Name]
FROM
  A LEFT JOIN B 
    ON A.[Security Id] = B.[Security Id]
    AND A.Account = B.Account
WHERE
  B.[Security Id] IS NULL

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40309

Tricky one! Try this (I can't test it, watch out for syntax errors):

SELECT BAccounts.Account, A.SecurityID, A.SecName
 from TableA  A
  cross join (select distinct Account
               from TableB) BAccounts
  left outer join TableB B
   on B.Account = BAccounts.Account
    and B.SecurityID = A.SecurityID
 where B.SecurityID is null

The logic is:

  • Start with TableA
  • Join each row with all possible accounts found in table B
  • Now left-outer-join it with Table B, using B's Account and A's SecurityId
  • If not found, B.SecurityId is null, and we have the offending Account via the subquery

Upvotes: 3

Related Questions