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
 Query to get the data from same table

Author  Topic 

Chow
Starting Member

4 Posts

Posted - 2013-11-19 : 11:10:04
I have table with 4 columns Environmnet, LocationName, SettingName, Settingvalue

Table looks like
Environmnet, LocationName, SettingName, Value
values are
Prod, All, abc, abcdefrr

Test, All, gfd, rerwe

All, Newyork, fgsdf, dfgs

Demo, DC, erger, stgt

All, virgenia, fgaer, sdfasd

production, Virgenia, dfgd, drtert



I want query that displays for the condition where environment = all or production and locationname =all or virgenia

Environmnet, LocationName, SettingName, Value,

Production, All, abc, abcdefrr

All, virgenia, fgaer, sdfasd

production, Virgenia, dfgd, drtert



I want to display data in above result .

Can any one help me with that

rthrth

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 all
select 'Test', 'All', 'gfd', 'rerwe' union all
select 'All', 'Newyork', 'fgsdf', 'dfgs' union all
select 'Demo', 'DC', 'erger', 'stgt' union all
select 'All', 'virgenia', 'fgaer', 'sdfasd' union all
select 'production', 'Virgenia', 'dfgd', 'drtert'
)

select *
from yourTable

where (environment = 'all' or environment = 'production')
and (locationname = 'all' or locationname = 'virgenia')

OUTPUT:

Environment LocationName SettingName Value
----------- ------------ ----------- --------
Production All abc abcdefrr
All virgenia fgaer sdfasd
production Virgenia dfgd drtert
[/code]

Be One with the Optimizer
TG
Go to Top of Page

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 you

rthrth
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

Chow
Starting Member

4 Posts

Posted - 2013-11-19 : 11:55:54
Thank u !!!

rthrth
Go to Top of Page

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 all
select 'Test', 'All', 'gfd', 'rerwe' union all
select 'All', 'Newyork', 'fgsdf', 'dfgs' union all
select 'Demo', 'DC', 'erger', 'stgt' union all
select 'All', 'virgenia', 'fgaer', 'sdfasd' union all
select 'production', 'Virgenia', 'dfgd', 'drtert'
)

select *
from yourTable

where (environment = 'all' or environment = 'production')
and (locationname = 'all' or locationname = 'virgenia')

OUTPUT:

Environment LocationName SettingName Value
----------- ------------ ----------- --------
Production, All, abc, abcdefrr
All, virgenia, fgaer, sdfasd
production, Virgenia , dfgd, drtert
Production, All, dfgd, drtert

If 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
Go to Top of Page

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, drtert
Production, All, dfgd, drtert

EDIT:
fixed typo

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -