How do I write the SQL query for multiple records.

Question

5.00/5 (1 vote)

See more:

SQL

SQL-Server

SQLite

, +

The following query checks whether value exist in particular columns or not.

SQL

Select * from ACCOUNT where 'UGEREA0000OE' in (MODIFYUSER,CREATEUSER)

however this is only working for single value....what can i use for multiple values i.e.

SQL

Select * from ACCOUNT where 'UGEREA0000OE','UGEREA0000OF' in (MODIFYUSER,CREATEUSER)

This is not working. Please let me knw how can i do this.
Thanks.

Posted 14-Jan-15 6:39am

lovejeet0707

Updated 14-Jan-15 7:00am

PIEBALDconsult

v2

Add a Solution

Comments

ZurdoDev 14-Jan-15 12:44pm

Just do WHERE 1 IN (a,b) OR 2 IN (a,b)

lovejeet0707 14-Jan-15 12:47pm

You mean this :
Select * from ACCOUNT where 1 in ('UGEREA0000OB','UGEREA0000OE') or 2 in ('UGEREA0000OB','UGEREA0000OE') in (MODIFYUSER,CREATEUSER) ??

However this is not working.

PIEBALDconsult 14-Jan-15 13:06pm

Something like Ryan suggest is probably the simplest, but you could also use a UNION or JOIN to a CTE if you like and your data allows.

lovejeet0707 14-Jan-15 13:08pm

Yeah its looking simple, but didnt work.
Can you re-write the query how it will be done

PIEBALDconsult 14-Jan-15 13:11pm

Can. Won't.

lovejeet0707 14-Jan-15 13:12pm

Great. Thx....I do it !

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-01-14T06:56:00

Solution 1

You can't supply multiple values to an SQL IN - you can only specify a single column on the left [of the IN keyword], and none on the right.
Try:

SQL

SELECT * FROM Acount WHERE MuodifyUser IN ('UGEREA0000OE','UGEREA0000OF') OR CreateUser IN ('UGEREA0000OE','UGEREA0000OF')

[edit]Typos, and a clarifcation - OriginalGriff[/edit]

Posted 14-Jan-15 6:56am

OriginalGriff

Updated 14-Jan-15 7:57am

v2

Comments

PIEBALDconsult 14-Jan-15 13:02pm

"you can only specify a single column of the left, and none "

Umm... what?

lovejeet0707 14-Jan-15 13:03pm

Sorry didnt get uhh ??

OriginalGriff 14-Jan-15 13:58pm

Typos...:sigh:

PIEBALDconsult 14-Jan-15 14:05pm

I allowed for that, but your statement still seems untrue (of SQL Server).

OriginalGriff 14-Jan-15 14:16pm

In what way?
I'm pretty sure the IN syntax is
exp IN (list)
And exp can only be a single value (one column for example), and list can't contain columns - it has to be constant values.
So to check if either of two columns is in a range of values, you need two INs and and OR - don't you?

Both W3Schools and MSDN seem to agree, pretty much (MSDN adds a subquery as permissible as the IN list, is all):
http://www.w3schools.com/sql/sql_in.asp
http://msdn.microsoft.com/en-us/library/ms177682.aspx

If I'm wrong, I'd be very happy about it (given IN is a PITA when you try to get "creative")! :)

PIEBALDconsult 14-Jan-15 14:50pm

someliteral IN ( somecolumn , someothercolumn ... )
Works fine for me. It may be a T-SQL thing, I don't know.

Richard Deeming 14-Jan-15 15:26pm

"MuodifyUser"? :)

OriginalGriff 14-Jan-15 15:43pm

Would you believe me if I told you it was the Welsh spelling?

No? Damn...