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
 tricky SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SiKth
Starting Member

Sweden
1 Posts

Posted - 02/19/2011 :  06:48:26  Show Profile  Reply with Quote
I'm pretty new to this and i need help with a SELECT problem.

From my search comes $city and $service
$city compares only to the column city and works fine!
But $service is not just one column, its the name of many columns.

ID - username - password - city - serviceA - serviceB - serviceC
01 - username1 - password1 - city1 - service1 - service1 - service1
02 - username2 - password2 - city2 - [empty ] - service2 - service2
03 - username3 - password3 - city3 - service3 - service3 - service3

I want the rows where $city=city and the $service column is not empty on that row! (witch service that is searched for)

Like this: SELECT * FROM sj_users WHERE city = '$city' AND '$service' IS NOT NULL
If I search for ex. serviceA I would get ID 01 and 03, but not 02 because its serviceA column is empty!

Is this possible?
Very great full for any help!! THANKS!

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 02/19/2011 :  07:21:41  Show Profile  Reply with Quote
It can be acheived using dynamic sql but dynamic sql have its own pros and cons. Have a look at:
http://www.sommarskog.se/dynamic_sql.html
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 02/19/2011 :  07:28:33  Show Profile  Reply with Quote
--Sample table with data
create table TabDyn
( Id int,
UserName varchar(50),
City varchar(50),
ServiceA varchar(50),
ServiceB varchar(50))

Insert into TabDyn
Select 1,'username1','city1','service1', 'service1' union all
Select 2,'username2','city2',null, 'service1' union all
Select 3,'username3','city3','service3', 'service3'


--Dynamic select logic start here
Declare @City varchar(50)
Declare @Service varchar(50)

Select @City='city1', @Service = 'ServiceA'

Exec ('Select * from TabDyn where city = '''+ @City + ''' or ' + @Service + ' is not null')
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 02/19/2011 :  07:40:04  Show Profile  Reply with Quote
if the number of "service" column is limited, you don't have to use Dynamic SQL


select *
from   yourtable
where  city = @city
and    (
           @service = 'service1' and service1 is not null
       or  @service = 'service2' and service2 is not null
       or  @service = 'service3' and service3 is not null
       )



KH
Time is always against us

Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 02/19/2011 :  17:24:42  Show Profile  Visit jcelko's Homepage  Reply with Quote
You are doing everything wrong.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

You have a repeated group in violation of 1NF. The column names are wrong. You put a $ in names. Did you mean an empty string or a NULL (we have no DDL)?





--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
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.22 seconds. Powered By: Snitz Forums 2000