Exclude rows with specific columns values from SELECT

Question

I've got table with following columns and data:

stock   quant
-----   -----
10      0
10     -5
10      1
1       20
10      1
10      88
5       1

What I need is to exclude rows with stock_status = 10 and quantity <= 0 at the same time.

I need to have those rows with stock_status = 10 but quantity > 0 or the other way around.

So the desire output from this would be

stock   quant
----    ---
10      1
1       20
10      1
10      88
5       1

Thanks.

so what have you tried so far to achieve this? any query you already tried? Also you wrote you want to have lines with stock = 10, but in your expected result set you have lines with 1 or 5? — Olli
– Olli, Commented Jun 3, 2014 at 12:50
I have tried HAVING and now im thinking about using CASE. It should be something like SELECT * FROM table WHERE stock != 10 (AND AT THE SAME TIME) quant <= 0 — JTC
– JTC, Commented Jun 3, 2014 at 12:53
that's called filtering and in SQL you do it through WHERE condition. so add a condition WHERE quantity > 0 — Rahul
– Rahul, Commented Jun 3, 2014 at 12:54
Here is a SQL Fiddle Displaying the answer... They beat me to it. sqlfiddle.com/#!6/a3896/3 — Tanner_Gram
– Tanner_Gram, Commented Jun 3, 2014 at 13:05

Jean-François Savard · Accepted Answer · 2015-03-05 14:11:57Z

10

Well you actually wrote the query yourself by telling us what you need to exclude...

SELECT stock, quant
FROM yourTable 
WHERE NOT(stock_status = 10 AND quantity <= 0);

You should follow a tutorial on SQL query (for example on W3school) as this is very basic and you should be able to do a query like that in less than a minute after following a very short tutorial for beginner.

I recommend this link : SQL Tutorial.

edited Mar 5, 2015 at 14:11

answered Jun 3, 2014 at 12:55

Jean-François Savard

21k7 gold badges54 silver badges78 bronze badges

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

Comments

Barmar · Accepted Answer · 2014-06-03 12:56:28Z

3

SELECT stock, quant
FROM yourTable
WHERE NOT (stock_status = 10 AND quantity <= 0)

or, apply de Morgan's Laws:

SELECT stock, quant
FROM yourTable
WHERE stock_status != 10 OR quantity > 0

answered Jun 3, 2014 at 12:56

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

Exclude rows with specific columns values from SELECT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related