T-SQL Filtering data with more than one condition by row

Question

T-SQL Filtering data with more than one condition by row

benkraiemchedlia

SSCommitted

Points: 1672
More actions
April 22, 2017 at 5:09 pm

#323124

Hi,
I want to select result filtered by more than a value by a row.
I don't know if it's possible or there's a way to make it work,
This is my example :

id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
4 1275, 1 14
I want to select row where type <> 14 and visible = 0 at the same row,
it means my query will return this result :

id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
Thank you for the help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

benkraiemchedlia - Saturday, April 22, 2017 5:09 PM
Hi,
I want to select result filtered by more than a value by a row.
I don't know if it's possible or there's a way to make it work,
This is my example :
id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
4 1275, 1 14
I want to select row where type <> 14 and visible = 0 at the same row,
it means my query will return this result :
id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
Thank you for the help

Wow, I have no idea what you logic is supposed to be with your description and sample data/output. Can you put text beside each output row describing the logic as to why it is included? Also state why row 4 did not make it into the output.

Also, did you know you can use parentheses, AND and OR in the WHERE clause? Seems to me you could combine those in some boolean-appropriate way to achieve your results.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

benkraiemchedlia - Saturday, April 22, 2017 5:09 PM
Hi,
I want to select result filtered by more than a value by a row.
I don't know if it's possible or there's a way to make it work,
This is my example :
id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
4 1275, 1 14
I want to select row where type <> 14 and visible = 0 at the same row,
it means my query will return this result :
id Answer visible type
1 answer1 0 14
2 Mexico 1 10
3 101202 0 2
Thank you for the help

According to TYPE <> 14, the row where ID = 1 should be excluded.
According to VISIBLE = 0, the row where ID = 2 should be excluded.
According to TYPE <> 14 AND VISIBLE = 0, the row where ID = 3 should be returned.
According to TYPE <> 14 AND VISIBLE = 0, the row where ID = 4 should be returned.

Is that correct? If not, then I'm with Kevin and have no clue how your given output matches your stated criteria and you need to explain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99133 More actions · Answer 3

This almost sounds like the OP is wants to do an XOR statement, so the logic wanted is? Visible = 0 XOR [Type] <> 14

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246986 More actions · Answer 4

Thom A - Sunday, April 23, 2017 6:36 AM
This almost sounds like the OP is wants to do an XOR statement, so the logic wanted is? Visible = 0 XOR [Type] <> 14

Seems to fit. The T-SQL implementation being something like this
SELECT columns FROM table WHERE NOT (TYPE = 14 AND VISIBLE <> 0)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 5

Phil Parkin - Sunday, April 23, 2017 7:15 AM
Thom A - Sunday, April 23, 2017 6:36 AM
This almost sounds like the OP is wants to do an XOR statement, so the logic wanted is? Visible = 0 XOR [Type] <> 14
Seems to fit. The T-SQL implementation being something like this
SELECT columns FROM table WHERE NOT (TYPE = 14 AND VISIBLE <> 0) AND ([Type] = 14 OR Visible <> 0);

Actually it would be this. (I've amended Phil's code).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Very cool analysis, guys.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

Thom A - Sunday, April 23, 2017 7:51 AM
Phil Parkin - Sunday, April 23, 2017 7:15 AM
Thom A - Sunday, April 23, 2017 6:36 AM
This almost sounds like the OP is wants to do an XOR statement, so the logic wanted is? Visible = 0 XOR [Type] <> 14
Seems to fit. The T-SQL implementation being something like this
SELECT columns FROM table WHERE NOT (TYPE = 14 AND VISIBLE <> 0) AND ([Type] = 14 OR Visible <> 0);
Actually it would be this. (I've amended Phil's code).

For once, I have to pull you up, Thom.
Here is a truth table for the assumed logic:

Thus, not (14 and not 0) captures all outcomes, without need for further embellishment.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 8

You are correct sir, due to the not equal part. It doesn't sit right with me though, XORs, in SQL, should be two part 😛

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DesNorton SSC-Insane Points: 24401 More actions · Answer 9

This seems to be the simplest form of the OP's requirement.
SELECT columns FROM table WHERE TYPE <> 14 OR VISIBLE = 0;

How to post data/code on a forum to get the best help.