select on multiple condititons or single condition

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

SQL Server 2005 Forums

Transact-SQL (2005)

select on multiple condititons or single condition

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2007-11-01 : 19:53:02

I have two tables
A bond table with columns BondNum, market value, country and currency
A derivative table with DerivNum, country and currency

I need a sql that creates a temp table will first join the two table on both country and currency and if there is nothing then join on currency alone and then sort by the market value and the precidene that the derivative matched on both and then only curreny

Example

D.Num B.Num B.Ctry B.Crncy D.Ctry D.Crncy MarketValue
1 1 US usd US usd 5000
1 2 US usd US usd 3000
1 3 US usd US usd 1000
1 4 CA usd US usd 5000
1 5 AU usd US usd 3000

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-01 : 22:21:25

If you outer join to your second table twice,

a) Currency and country
b) currency but not country (or country is null or whatever you need to identify the row for your second case)

you get both rows along with your orignal. From there you can work out which to use using the nullness of the forign key on your outer joined tables.
I don't understand what you are talking about with the "precidene" part of it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources