SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query to get the data from same table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chow
Starting Member

USA
4 Posts

Posted - 11/19/2013 :  11:10:04  Show Profile  Reply with Quote
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

Edited by - Chow on 11/19/2013 11:23:57

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/19/2013 :  11:24:06  Show Profile  Reply with Quote

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


Be One with the Optimizer
TG
Go to Top of Page

Chow
Starting Member

USA
4 Posts

Posted - 11/19/2013 :  11:27:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/19/2013 :  11:33:01  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 11/19/2013 :  11:55:54  Show Profile  Reply with Quote
Thank u !!!

rthrth
Go to Top of Page

Chow
Starting Member

USA
4 Posts

Posted - 11/19/2013 :  12:36:24  Show Profile  Reply with Quote
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

Edited by - Chow on 11/19/2013 12:39:57
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/19/2013 :  13:43:12  Show Profile  Reply with Quote
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

Edited by - TG on 11/19/2013 13:55:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000