Multiple Values in SQL Query

Question

I'm learning SQL and I'm trying to run a query where the results return with orders from several product numbers. However, I think I'm making a mistake when it comes to listing those product numbers in the filter of my query. I am certain that at least 2 orders have been placed of product 1201 as I have run the numbers in individual queries, but when I add additional numbers, I get zero results.

My query:

SELECT 
   h.customer_number, h.product_number, h.del_qty
FROM 
   customer_del_hist AS h 
INNER JOIN 
   inv_master AS i ON i.product_number = h.product_number
WHERE 
   (i.product_number = '1200, 1201, 1202, 1203')

Gordon Linoff · Accepted Answer · 2015-03-19 23:17:06Z

4

You want in. If these are numbers, then you do not want single quotes either:

WHERE i.product_number IN (1200, 1201, 1202, 1203)

If, you are getting a datatype error, then the values are not numbers. Use single quotes in that case:

WHERE i.product_number IN ('1200', '1201', '1202', '1203')

(Why a field called "product_number" would not have a number is an interesting self-referential paradox.)

edited Mar 19, 2015 at 23:17

answered Mar 18, 2015 at 12:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

E. Peterson Over a year ago

I modified the query accordingly and received the following error: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '006R01179XER' to data type int.

Jen R Over a year ago

If your i.product_number field is not actually a number (as the error suggests) you can compare the strings instead, placing the single quotes around each product_number and using the comma as a delimiter: WHERE i.product_number IN ('1200', '1201', '1202', '1203')

koushik veldanda · Accepted Answer · 2015-03-18 12:20:01Z

1

You Query(ie: '=" )searches for the '1200, 1201, 1202, 1203' string not the values present in that place you need to use in operator for multiple values or as

WHERE  i.product_number  in(1200,1201,1202)

You can go with

WHERE i.product_number =1200 OR i.product_number =1201 OR i.product_number =1202 OR i.product_number =1203

answered Mar 18, 2015 at 12:20

koushik veldanda

1,10710 silver badges23 bronze badges

Comments

matcheek · Accepted Answer · 2015-03-18 12:20:17Z

1

Your can further extend Gordon's answer by using subquery

where i.product_number in (select id from all_products)

answered Mar 18, 2015 at 12:20

matcheek

5,20510 gold badges51 silver badges78 bronze badges

Comments

Kalman · Accepted Answer · 2015-03-18 18:21:25Z

0

Use WHERE IN clause WHERE in ('1200, 1201, 1202, 1203')

edited Mar 18, 2015 at 18:21

Kalman

8,1311 gold badge29 silver badges45 bronze badges

answered Mar 18, 2015 at 13:29

ASP.Net Developer

3315 silver badges15 bronze badges

Collectives™ on Stack Overflow

Multiple Values in SQL Query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related