Author |
Topic |
Chow
Starting Member
4 Posts |
Posted - 2013-11-19 : 11:10:04
|
I have table with 4 columns Environmnet, LocationName, SettingName, SettingvalueTable looks like Environmnet, LocationName, SettingName, Value values are Prod, All, abc, abcdefrrTest, All, gfd, rerweAll, Newyork, fgsdf, dfgsDemo, DC, erger, stgtAll, virgenia, fgaer, sdfasd production, Virgenia, dfgd, drtertI want query that displays for the condition where environment = all or production and locationname =all or virgeniaEnvironmnet, LocationName, SettingName, Value, Production, All, abc, abcdefrrAll, virgenia, fgaer, sdfasd production, Virgenia, dfgd, drtertI want to display data in above result .Can any one help me with thatrthrth |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-19 : 11:24:06
|
[code]with yourTable (Environment, LocationName, SettingName, Value)as(select 'Production', 'All', 'abc', 'abcdefrr' union allselect 'Test', 'All', 'gfd', 'rerwe' union allselect 'All', 'Newyork', 'fgsdf', 'dfgs' union allselect 'Demo', 'DC', 'erger', 'stgt' union allselect 'All', 'virgenia', 'fgaer', 'sdfasd' union allselect 'production', 'Virgenia', 'dfgd', 'drtert')select * from yourTablewhere (environment = 'all' or environment = 'production')and (locationname = 'all' or locationname = 'virgenia')OUTPUT:Environment LocationName SettingName Value----------- ------------ ----------- --------Production All abc abcdefrrAll virgenia fgaer sdfasdproduction Virgenia dfgd drtert[/code]Be One with the OptimizerTG |
|
|
Chow
Starting Member
4 Posts |
Posted - 2013-11-19 : 11:27:00
|
Thank you So much ..I missed parenthesis for reach condition so it is returning different results ...Thank yourthrth |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-19 : 11:33:01
|
No prob. Yeah, parentheses are critical when combining ANDs and ORs.Be One with the OptimizerTG |
|
|
Chow
Starting Member
4 Posts |
Posted - 2013-11-19 : 11:55:54
|
Thank u !!!rthrth |
|
|
Chow
Starting Member
4 Posts |
Posted - 2013-11-19 : 12:36:24
|
with yourTable (Environment, LocationName, SettingName, Value)as(select 'Production', 'All', 'abc', 'abcdefrr' union allselect 'Test', 'All', 'gfd', 'rerwe' union allselect 'All', 'Newyork', 'fgsdf', 'dfgs' union allselect 'Demo', 'DC', 'erger', 'stgt' union allselect 'All', 'virgenia', 'fgaer', 'sdfasd' union allselect 'production', 'Virgenia', 'dfgd', 'drtert')select * from yourTablewhere (environment = 'all' or environment = 'production')and (locationname = 'all' or locationname = 'virgenia')OUTPUT:Environment LocationName SettingName Value----------- ------------ ----------- --------Production, All, abc, abcdefrrAll, virgenia, fgaer, sdfasdproduction, Virgenia , dfgd, drtertProduction, All, dfgd, drtertIf I run above qury and if it will return duplicates for setting name 'dfgd' (above query wont return the above result , it it is the case)How can I eliminate the duplicate based on environment and LocationName.rthrth |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-19 : 13:43:12
|
should one of these values take precedence over the other? In other words which one of these rows do you want and why?production, Virgenia , dfgd, drtertProduction, All, dfgd, drtertEDIT:fixed typoBe One with the OptimizerTG |
|
|
|
|
|