Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mauich123
Starting Member
21 Posts |
 Posted - 2011-03-11 : 07:18:07
|
HiI have 2 transactiontables that I would like to join (for comparison). This works fine as long as an order has the same number of transactions but if one table has 3 transactions I get problems. This is probably pretty basic but I just can't see whats wrongSELECTamountA,amountB = CASE t1.transactiontype WHEN 'return' THEN ((t1.amountB -t1.amountB )*-1) ELSE t1.amountB END, t1.orderno, t1.transactiontype,t1.currency FROM AccountingTbl t1FULL OUTER JOIN PaymentTbl t2 on t1.orderno=t2.orderno and t1.transactiontype=t2.transactiontype where t2.orderno='123456'This gives me:amountA amountB orderno transactiontype3555 3555 123456 invoice-3495 -3555 123456 return-60 -3555 123456 return BUT, t1 actually only have 2 rows. So the last amountB should be NULL, or at least I want it to be NULL and not a copy of the row above.. What am I missing here?Thanks |
|
|
|
|
|
JOIN Question