?
Solved

very, very important, join logic problem

Posted on 2007-07-31
13
Medium Priority
?
226 Views
Last Modified: 2008-01-09
tableA - acctid, symbol, side, price, quantity
tableB - acctid, symbol, side, price, quantity

there is no unique identifier
tableA symbol,side,price,quantity should equal tableB symbol,side,price,quantity

i just now removed 4 orders from tableA -- one for each of the acctid's i'm looking at
now i need to write the logic to find 'missing' orders from tableB where
tableA.acctid=tableB.acctid
tableA.symbo=tableB.symbol
tableA.side=tableB.side
tableA.price = tableB.price
tableA quantity = tableB.quantity

basically, where count is not the same for symbol,side,price,quantity coutn is not the same for each acctid, show me the 'missing' orders

like i said, i just removed 4 orders from tableB
expecting to return 4 orders in my select
i got waaaaaaaaaaaaay too many back

surely my join is poorly done, this is pretty urgent, can anyone advise?
....
.......
from tableA a left outer join tableB b
  on a.acctid = b.acctid
  and a.symbol = b.symbol
  and a.price = b.price
  and a.qty = b.qty
  and a.side = b.side
where
  b.tradecount is null  (just a count(*) of the above, i pumped it into a temp table)

please advise
 
0
Comment
Question by:dbaSQL
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 19602459
does anybody have a suggestion?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 19602515
You don't need a unique identifier, just some combination of columns that assures uniqueness.  Then, just join on those columns and test for null on those columns.   Without some kind of uniqueness, the task is logically impossible.  

This may be overkill, but...

from tableA a left outer join tableB b
  on a.acctid = b.acctid
  and a.symbol = b.symbol
  and a.price = b.price
  and a.qty = b.qty
  and a.side = b.side
where b.acctid is null    
  and b.symbol is null
  and b.price is null
  and b.qty is null
  and b.side is null
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19602653
that's what i thought, too, but this is what i'm getting:

create table #a(acctid varchar(16), symbol varchar(16), side char(1), price money, qty bigint, tradecount bigint)
create table #b(acctid varchar(16), symbol varchar(16), side char(1), price money, qty bigint, tradecount bigint)

insert #b
SELECT acctid,Symbol,side,price,quantity,count(*) AS TradeCount
FROM tableB  WITH (NOLOCK)
WHERE tradeTime BETWEEN (SELECT MIN(exectime) FROM tableA) AND '2007-07-27 15:15:00'
AND acctid IN ('a01','a012','a0123')
AND endpoint = 'CCC'
GROUP BY acctid, symbol, side, price, qty
ORDER BY  acctid, symbol, side, price, aty


insert #a
select acctid, symbol,side,qty,price,count(*) as tradecount
from tableA
WHERE acctid IN ('a01','a012','a0123')
and endpoint = 'CCC'
group by acctid,symbol,side,price,qty
order by acctid,symbol,side,price,qty

---gives me 2321
select count(*) from tableA WHERE acctid IN ('a01','a012','a0123') and endpoint = 'CCC'  
---gives me 2317
select count(*) from tableB WHERE tradetime between (SELECT MIN(exectime) FROM tableA) AND '2007-07-27 15:15:00' and acctid IN ('a01','a012','a0123') and endpoint = 'CCC'

--this gives me back 1139 records -- i'm looking for 4
select a.acctid, a.symbol, a.side,a.price,a.qty, coalesce(a.tradecount - b.tradecount,a.tradecount) as tradecount
from #a a left outer join #b b
on a.acctid = b.acctid
  and a.symbol = b.symbol
  and a.price = b.price
  and a.qty = b.qty
  and a.side = b.side
where b.acctid IS NULL
AND b.Symbol IS NULL
AND b.side IS NULL
AND b.Price IS NULL
AND b.Qty IS NULL
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19603132
SELECT a.acctid, a.symbol, a.price, a.qty, a.side,
    (aCount - bCount) AS [# Recs Missing]
FROM (
    SELECT acctid, symbol, price, qty, side, COUNT(*) AS aCount
    FROM tableA
    GROUP BY acctid, symbol, price, qty, side
) AS a
INNER JOIN (
    SELECT acctid, symbol, price, qty, side, COUNT(*) AS bCount
    FROM tableB
    GROUP BY acctid, symbol, price, qty, side
) AS b
ON a.acctid = b.acctid
  and a.symbol = b.symbol
  and a.price = b.price
  and a.qty = b.qty
  and a.side = b.side
  and aCount <> bCount
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19603135
If you prefer, you could join to a table of sequential numbers and list a separate row for every missing row.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 19603238
What explains that to me is this:

Table #A has 2321 records
Table #B has 2317 records
Table #A has 1139 records that don't match table #B records  
Table #A has 1182 records that do match table #B records
Table #B has 1182 records that do match table #A records
Table #B has 1135 records that don't match table #A records


Do a right join in your last query. I'm betting it returns 1135 records, which confirms my hunch.  
0
 
LVL 7

Assisted Solution

by:natloz
natloz earned 450 total points
ID: 19603280
You simply need a left join and look for NULLs

select b.acctid from tableA a
left join tableB b on b.acctid = a.acctid
where b.acctid is null
0
 
LVL 7

Expert Comment

by:natloz
ID: 19603290
or the reverse would be (get missing values in tableA that exist in tableB

select a.acctid from tableB b
left join tableA a on a.acctid = b.acctid
where a.acctid is null
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19603547
i found my original problem - two of my fields were reversed --

insert #a  
select acctid, symbol,side,qty,price,count(*) as tradecount  from tableA

create table #a(acctid varchar(16), symbol varchar(16), side char(1), price money, qty bigint, tradecount bigint)

i was putting price into qty, and vice verse

but, scott, i figured i'd try yours given you've got some pretty nice tsql out here....i'm not sure why your example is giving me back zero ??

no biggie, though, as i said.  i found my original problem.
having corrected the attributes
i'm finding the four missing orders

so.  my test works.  now i have to wrap this up pretty and proceduralize it such that it can be used on demand, in production, when there are outages on or relating to the varying exchanges

any advice?
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 450 total points
ID: 19604287
What can unsuspectingly cause the above situation is when a column of #A and #B are both NULL (meaning it was null in the original TABLEA), then the outer join will treat #A.column = #B.column like they do NOT match even when the both contain NULL.

See if this returns your 4 rows:

select a.acctid, a.symbol, a.side,a.price,a.qty, coalesce(a.tradecount - b.tradecount,a.tradecount) as tradecount
from #a a left outer join #b b
on (a.acctid = b.acctid OR a.acctId IS NULL)
  and (a.symbol = b.symbol or a.symbol is null)
  and (a.price = b.price or a.price is null)
  and (a.qty = b.qty or a.qty is null)
  and (a.side = b.side or a.side is null)
where b.acctid IS NULL
AND b.Symbol IS NULL
AND b.side IS NULL
AND b.Price IS NULL
AND b.Qty IS NULL
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 600 total points
ID: 19604376
That's a good point about NULLs -- see if this returns the rows you're missing:

SELECT a.acctid, a.symbol, a.price, a.qty, a.side,
    (aCount - bCount) AS [# Recs Missing]
FROM (
    SELECT acctid, symbol, price, qty, side, COUNT(*) AS aCount
    FROM tableA
    GROUP BY acctid, symbol, price, qty, side
) AS a
INNER JOIN (
    SELECT acctid, symbol, price, qty, side, COUNT(*) AS bCount
    FROM tableB
    GROUP BY acctid, symbol, price, qty, side
) AS b
ON (a.acctid = b.acctid OR (a.acctid IS NULL AND b.acctid IS NULL))
  and (a.symbol = b.symbol OR (a.symbol IS NULL AND b.symbol IS NULL))
  and (a.price = b.price OR (a.price IS NULL AND b.price IS NULL))
  and (a.qty = b.qty OR (a.qty IS NULL AND b.qty IS NULL))
  and (a.side = b.side OR (a.side IS NULL AND b.side IS NULL))
  and aCount <> bCount
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19604680
nope, that one comes back empty, too
odd, though, the one i fixed is finding them
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19626482
i am going to split the points and close on this thing.  as i said, i found my problem myself, but i find the input from each of you to be quite valuable.  (helpful, useful, you know what i mean)
thank you very much
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question