?
Solved

sql query

Posted on 2013-12-17
6
Medium Priority
?
319 Views
Last Modified: 2014-04-10
i am using volusion store www.volusion.com

i have created a category name 'available products'

i want to filter all whats available in stock, i am using the following code
StockStatus <= 100

it filters quantity available between 1 to 100
but when quantity becomes +0 or -1 still its showing in available products
can someone help to modify the code
0
Comment
Question by:ammadeyy2020
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39724362
StockStatus between 1 and 100

or

StockStatus >= 1 AND StockStatus <=100
0
 

Author Comment

by:ammadeyy2020
ID: 39724380
i am using StockStatus <= 100

when quantity becomes 0 or - i dont want that product to be in available
0
 
LVL 18

Expert Comment

by:x-men
ID: 39724400
SELECT ª FROM mytable WHERE (StockStatus >= 1 AND StockStatus <=100)
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:ammadeyy2020
ID: 39724411
i think some problem in query, its givin error message

An error has occurred. Please try your request again, or contact customer service for assistance. Thank you.

when i remove the query its ok
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39725421
The query by x-men should work fine. Are you working in SQL Server or MySQL? You can try with BETWEEN..AND also.

SELECT * FROM mytable WHERE StockStatus BETWEEN 1 AND 100
0
 
LVL 1

Accepted Solution

by:
JokoLennono earned 2000 total points
ID: 39790182
SELECT *
FROM products_joined
WHERE
StockStatus IS NOT NULL
AND StockStatus > 0
AND StockStatus <= 100

if you want it to pull only products that have something specific in the product code or product name, then you'd filter it even further with something like:

SELECT *
FROM products_joined
WHERE
StockStatus IS NOT NULL
AND StockStatus > 0
AND StockStatus <= 100
AND productcode LIKE '%ABC%'
AND productname LIKE '%SELL THIS NOW%'

This will pull all products that you have in stock with a stock status between 1 and 100, and the product code contains "ABC" and the product name contains "SELL THIS NOW".

Best of luck
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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