How to get highest of 3 values in separate tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

How to get highest of 3 values in separate tables?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2009-10-21 : 10:53:58

Bit of curious one this! I've got controls being created from a main table. Problem is that there is an order of precedence on where the data comes from.

ControlDeclaration = Default Value
Global Declaration = Overrides default value
Instance Declaration = Overrides Global and Default

e.g.


Controls
---------------------------------------------
Name          Data
---------------------------------------------
Menu          1
Listing       1
Listing 2     1

Global
---------------------------------------------
Name          Data
---------------------------------------------
Menu Clone    74
Listing       NULL
Listing 2     81

Single Instance
---------------------------------------------
Name          Data
---------------------------------------------
Menu Clone    NULL
Listing       NULL
Listing 2     73

So the result I require would be


Required Results
---------------------------------------------
Name          Data
---------------------------------------------
Menu Clone    74
Listing       1
Listing 2     73

I hope that Makes sense, hard to explain! I could do a join on the 3 tables but am guessing that would be pretty heavy and I aren't sure how I'd get the priority right!

Any ideas how I could approach this problem?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-21 : 16:54:31

So it's more of a hierarchy thing as opposed to a max value, right? Assuming there rows for each control in all tables (as your sample indicates) You could INNER JOIN all three tables by [name], then:
SELECT COALESCE(instance.Data, global.data, controls.data) as [data]

If global and instance tables don't have all control names then LEFT OUTER JOIN to those tables.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources