Skip to main content
C
cfnew
Known Participant
June 29, 2009
Answered

how to write select query for this

  • June 29, 2009
  • 2 replies
  • 1415 views

Hi,

I had a html form and in the for i had drop down box and it needs to select multiple values from the drop down box. When i select multiple values then i have to write the SQL select statement  query .

When i try to write the select statement and trying to run i am getting error.

select * from Table

where emo_no = '1,2,3'

this is how i write query please suggest me how  to write query for selecting multiple values from the drop down box.

Thanks

This topic has been closed for replies.
Correct answer Dan_Bracuk

Use the sql keyword "in".  If you don't know how, I've heard good things about the book Teach Yourself SQL in 10 Minutes by Ben Forta.

2 replies

A
Anonymous
August 6, 2009

select * from Table

where emo_no in ( 1,2,3)

for integer values

select * from Table

where emo_no in ('1','2','3')

for characters

If we talk about large scale applications that may have millions of records, I would suggest this.

declare @t table (v int)

insert into t (v) values (1)

insert into t (v) valves (2)

insert into t (v) values (3)

select *

from table

     inner join @t t on table.emo_no = t.v

Using "in" for a where clause is not so bad for filtering on a few values, but if you are filtering a lot of rows and a lot of values (emo_no) the performance degrades quickly for some reasons beyond the scope of this.

This is just one solution, I'll through this out as well, instead of an in memory (@t) table, doing a disk based temp table (#t) and creating an index on the column "v".

create table #t (v int)

insert into #t (v) values (1)

insert into #t (v) valves (2)

insert into #t (v) values (3)

create index ix_t on #t (v)

select *

from table

     inner join #t t on table.emo_no = t.v

drop table #t

Pardon any syntax errors and careful using a drop statement.

Sometimes in memory tables work better than disk temp tables, it takes some testing and trial and error depending on your datasets to determine the best solution.

Probably too much info  ;-)

Byron Mann

mannb@hostmysite.com

byronosity@gmail.com

Software Architect

hosting.com | hostmysite.com

http://www.hostmysite.com/?utm_source=bb

D
Dan_Bracuk
Inspiring
August 6, 2009

I have a recent experience, not done in Cold Fusion, but with similarities to Byron's post.  One similarity was that I was looking for results where some field was in a list of only 3 values.  It went something like this.

the 3 values I put in a table that we'll call t1.  I had to pull records from table t2 which has about 40 million records and table t3 which has about 8 million.  A common field we'll call f1, which is indexed in tables t2 and t3.  Logically, this query was correct.

select whatever

from t1 join t2 using (f1)

union

select whatever

from t1join t3 using (f1)

but it crawled.  It took several minutes to return results.

However, this took a second or two.

select whatever

from t1 join t2 using (f1)

where t2.f1 in (select f1 from t1)

union

select whatever

from t1 join t3 using (f1)

where t2.f1 in (select f1 from t1)

RBDMS is redbrick

Those are the observations.  Conclude whatever you want.

D
Dan_BracukCorrect answer
Inspiring
June 29, 2009

Use the sql keyword "in".  If you don't know how, I've heard good things about the book Teach Yourself SQL in 10 Minutes by Ben Forta.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices