# 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)

LVL 18
###### Who is Participating?

Senior 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

Author Commented:
does anybody have a suggestion?
0

Commented:
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

Author 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
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
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
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

Senior 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

Senior DBACommented:
If you prefer, you could join to a table of sequential numbers and list a separate row for every missing row.
0

Commented:
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

Commented:
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

Commented:
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

Author 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

0

Commented:
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:

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

Author Commented:
nope, that one comes back empty, too
odd, though, the one i fixed is finding them
0

Author 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.