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.
Author |
Topic |
SiKth
Starting Member
1 Post |
Posted - 2011-02-19 : 06:48:26
|
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 - serviceC01 - username1 - password1 - city1 - service1 - service1 - service102 - username2 - password2 - city2 - [empty ] - service2 - service203 - username3 - password3 - city3 - service3 - service3 - service3I 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 NULLIf 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
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-02-19 : 07:21:41
|
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 |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2011-02-19 : 07:28:33
|
--Sample table with datacreate table TabDyn( Id int, UserName varchar(50), City varchar(50), ServiceA varchar(50), ServiceB varchar(50))Insert into TabDynSelect 1,'username1','city1','service1', 'service1' union allSelect 2,'username2','city2',null, 'service1' union allSelect 3,'username3','city3','service3', 'service3' --Dynamic select logic start hereDeclare @City varchar(50)Declare @Service varchar(50)Select @City='city1', @Service = 'ServiceA'Exec ('Select * from TabDyn where city = '''+ @City + ''' or ' + @Service + ' is not null') |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-02-19 : 07:40:04
|
if the number of "service" column is limited, you don't have to use Dynamic SQLselect *from yourtablewhere city = @cityand ( @service = 'service1' and service1 is not null or @service = 'service2' and service2 is not null or @service = 'service3' and service3 is not null ) KH[spoiler]Time is always against us[/spoiler] |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-02-19 : 17:24:42
|
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.htmlIf 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/1593271905You 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|