Select By Joining and finding different Qty

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select By Joining and finding different Qty

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2011-03-29 : 17:20:20

I have two simple tables:

Table1 Table2

Acct Acct
Cusip Cusip
Quantity Quantity

1) All I want to do is lock the two tables together by Acct and Cusip and show me the ones that have a different Quantity.
Ex. Acct "1" and Cusip "ABC" are in both Tables but Table2 has the Quantity as 10 while Table 1 has Quantity of 5. I would like to show something like:

Acct Cusip Table1 Table2
1 ABC 5 10

Only the ones with a difference in Quantity should show up on this select and just ignore the rest that are the same.

Thanks!

------------------------------
Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2
Be kind to the newbies because you were once there.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-29 : 18:09:19

You can do something like this:

select
   isnull(t1.Acct,t2.Acct) as Acct,
   isnull(t1.Cusip,t2.Cusip) as Cusip,
   t1.Quantity,
   t2.Quantity,
   isnull(t1.Quantity,0) - isnull(t2.Quantity,0) as Diff
from
   Table1 t
   full join Table2 t2
      on t1.Acct = t2.Acct and t1.Cusip = t2.Cusip
where
   t1.Quantity is null
   or t2.Quantity is null
   or t1.Quantity <> t2.Quantity

Edit: This would also find Cusips that exist in one table but not in the other. If you did not want that - that is, you want to get ONLY cusips that exist in both tables, change the "full join" to "inner join"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources