We help IT Professionals succeed at work.

SQL Select Multiple Items in one statement?

1,254 Views
Last Modified: 2012-05-06
What is the shortest, most efficient way to combine the following into one statement, if possible?

Select Word from Wordlist where location = w1 and number = 13
Select Word from Wordlist where location = w2 and number = 22
Select Word from Wordlist where location = w3 and number = 18
Select Word from Wordlist where location = w4 and number = 35
Select Word from Wordlist where location = w5 and number = 13
Select Word from Wordlist where location = w6 and number = 44
Select Word from Wordlist where location = w7 and number = 39
Select Word from Wordlist where location = w8 and number = 8

please note that number is not an identity column, and I cannot go by the identity column at all.
 
Thanks for your help!
Comment
Watch Question

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
Hi arthurh88,

two options come to mind ...

-- 1
Select Word from Wordlist where location = w1 and number = 13
UNION ALL
Select Word from Wordlist where location = w2 and number = 22
UNION ALL
Select Word from Wordlist where location = w3 and number = 18
UNION ALL
Select Word from Wordlist where location = w4 and number = 35
UNION ALL
Select Word from Wordlist where location = w5 and number = 13
UNION ALL
Select Word from Wordlist where location = w6 and number = 44
UNION ALL
Select Word from Wordlist where location = w7 and number = 39
UNION ALL
Select Word from Wordlist where location = w8 and number = 8

--2
Select Word from Wordlist where (location = w1 and number = 13)
or (location = w2 and number = 22)
or (location = w3 and number = 18)
or (location = w4 and number = 35)
or (location = w5 and number = 13)
or (location = w6 and number = 44)
or (location = w7 and number = 39)
or (location = w8 and number = 8)

lwadwell

Author

Commented:
hi there.  are you sure the OR statement is appropriate rather than an AND?

 I need to select all of those items and have 8 values returned.

I'm using an ASP.NET application and I need to grab all of those items from the dataserver, to dump the 8 values into a web page.

Author

Commented:
I need the output to be in the form of columns, W1 through W8.  I tried the UNION statement and it almost looked ok, except that the output was in one single column titled 'Word'.

I want the output to be a single row with Column Names W1, W2, W3, W4, W5, W6, W7, W8
Project Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thank you iwadwell.  That worked great, and thanks for explaining the OR as well!  

Author

Commented:
bravo!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.