very, very important, join logic problem

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
 
LVL 18
dbaSQLAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
dbaSQLAuthor Commented:
does anybody have a suggestion?
0
 
dqmqCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dbaSQLAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
If you prefer, you could join to a table of sequential numbers and list a separate row for every missing row.
0
 
dqmqCommented:
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
 
natlozCommented:
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
 
natlozCommented:
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
 
dbaSQLAuthor Commented:
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
 
dqmqCommented:
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
 
dbaSQLAuthor Commented:
nope, that one comes back empty, too
odd, though, the one i fixed is finding them
0
 
dbaSQLAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.