Solved

SQL "OR" Clause question

Posted on 2012-03-26
5
328 Views
Last Modified: 2012-03-26
Is there a more simple way to do this instead of using the or clause?
SELECT  * from mtytable where (accountNo='1' or accountNo='345' or accountNo='2' or accountNo='4' or accountNo='4000' or ...)

I'm looking for something like
SELECT  * from mtytable where accountNo in ('1', '345', '2','4',...... )


Thanks!
0
Comment
Question by:chrisli
  • 3
5 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 200 total points
ID: 37766908
Yes, exactly as you coded using the "IN ()" construct.
:p
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766936
if your account numbers are in fact NUMBERS then you should remove the quotes, otherwise your syntax is correct as is


that would apply to both the OR and the IN versions
0
 

Author Comment

by:chrisli
ID: 37766975
I'm feeling pretty stupid now... But how come this is not returning any results?

DECLARE @accounts VARCHAR(max)
Select @accounts = COALESCE( '''' + LTRIM(RTRIM(AccountNo))+ ''', ' + @accounts,' ''' + LTRIM(RTRIM(AccountNo)) + '''') from mytable where [type]='newAccts'

SELECT     * FROM theothertable WHERE    (accountNo IN (@accounts))
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766987
because you are using a variable.  a variable (a single value) and a list of values aren't the same thing.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 37766997
don't bother trying to construct the list,  simply use the query itself as the IN condition


select * from theothertable where accountno in
(select accountno from mytable where [type]='newAccts')
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
delete the first occurence of a duplicate row in sql 5 38
Fill Null values 5 28
Trouble with <> 2 20
sql server query 12 25
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question