CODEWITHSUNDEEP
sqlsql-serverfilter
E. Peterson
E. Peterson

Reputation: 452

Multiple Values in SQL Query

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')

Upvotes: 0

Views: 81

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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.)

Upvotes: 4

ASP.Net Developer
ASP.Net Developer

Reputation: 331

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

Upvotes: 0

matcheek
matcheek

Reputation: 5147

Your can further extend Gordon's answer by using subquery

where i.product_number in (select id from all_products)

Upvotes: 1

koushik veldanda
koushik veldanda

Reputation: 1107

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

Upvotes: 1

Related Questions