SELECT statement -- list of fields in WHERE clause

Dave · 09-13-2008, 02:18 PM

Hi --

Can the list of fields in the WHERE clause of the following SELECT statement be written more efficiently? I used to use a program with an INLIST() option in which several fields could just be listed: WHERE INLIST(fieldname,"value1","value2",etc.) Can't find anything in MySQL, but then I don't have much experience...Thanks for any help!

Dave

Code:

select distinct coursetitle, statecourse, localcourse, semester, section, teachername
   from mytable
   where (semester = "0" or semester = "1") 
         and coursetitle is not null 
         and (statecourse = "1021X" or
              statecourse = "2020X" or
              statecourse = "2023X" or
              statecourse = "2024X" or
              statecourse = "2030X" or
              statecourse = "3010X" or
              statecourse = "3020X" or
              statecourse = "3038X" or
              statecourse = "3050X" or
              statecourse = "3060X" or
              statecourse = "4021X" or
              statecourse = "4024X" or
              statecourse = "4052X"
             )
   order by statecourse;

xenon · 09-13-2008, 03:08 PM

you probably mean...

Code:

SELECT ... FROM tbl WHERE field_name IN('value1', 'value 2'[, ...])

ETbyrne · 09-13-2008, 03:32 PM

I really need to study up on my SQL statements...

Dave · 09-14-2008, 01:20 AM

Exactly what the doctor ordered!

Thank you,
Dave

EyeDentify · 09-16-2008, 11:16 AM

And you should remember to use two eaqual "==" when comparing variables.... or else it will asign.

i fall for that one myself time to times.

Oh sorry. Didn´t see that it was in the SQL. Then it´s fine i guess.

Dave · 09-16-2008, 07:13 PM

A good thing to ALWAYS be aware of, though. It has tripped me up more than once, and, unfortunately, I'm sure it will in the future. I need a wall poster of [...can't make up my mind...Pam Anderson?..Sarah Palin..?..Satan?...] with the "==" rule plastered underneath!

Thanks,
Dave

ETbyrne · 09-17-2008, 01:54 AM

Quote:

I need a wall poster of [...can't make up my mind...Pam Anderson?..Sarah Palin..?..Satan?...]

How about Bill Clinton? lol

Dave · 09-17-2008, 11:36 AM

No, Bill Clinton's photo would appear over the "is-is" rule, not the "==" rule.