Filter rows based on two columns

Question

Filter rows based on two columns

cidr

Ten Centuries

Points: 1353
More actions
December 11, 2012 at 3:50 am

#286076

Hi there,
Apologies for the elusive subject title. I'm trying to filter only rows on one field only when another field has a certain value.
I have a query with several columns. One column (called NAME) has only two values so to keep this simple for the question the values are
Paul
Jim
This column will only ever have these two values for all the rows.
I have another column (called POSITION) that only ever has three values
Staff
Manager
Executive
I want to filter rows on another column that only applies to Paul.
For example
Where NAME is = to PAUL only include rows from POSITION where they are MANAGER.
If the NAME is Jim, the filter will not apply to POSITION.
I'm unsure how to achieve this filter without affecting rows where the NAME is Jim.
I hope this makes sense.
What would be the easiest way to do this on the WHERE clause?
Many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

cidr (12/11/2012)
Hi there,
Apologies for the elusive subject title. I'm trying to filter only rows on one field only when another field has a certain value.
I have a query with several columns. One column (called NAME) has only two values so to keep this simple for the question the values are
Paul
Jim
This column will only ever have these two values for all the rows.
I have another column (called POSITION) that only ever has three values
Staff
Manager
Executive
I want to filter rows on another column that only applies to Paul.
For example
Where NAME is = to PAUL only include rows from POSITION where they are MANAGER.
If the NAME is Jim, the filter will not apply to POSITION.
I'm unsure how to achieve this filter without affecting rows where the NAME is Jim.
I hope this makes sense.
What would be the easiest way to do this on the WHERE clause?
Many thanks

WHERE (NAME = 'Paul' and POSITION = 'MANAGER') or NAME = 'Jim'

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

nigelrivett SSCertifiable Points: 5472 More actions · Answer 2

where (name = 'Paul' and position = 'manager')

or name = 'Jim'

Cursors never.
DTS - only when needed and never to control.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

nigelrivett (12/11/2012)
where (name = 'Paul' and position = 'manager')
or name = 'Jim'

?? looks remarkably like a lowercase version of my solution 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

nigelrivett SSCertifiable Points: 5472 More actions · Answer 4

Yep - odd. Yours wasn't visible when I typed mine in but from the post dates they are 20 mins apart.

No they are 1 min apart - sorry I didn't predict that you were going to post that while I was typing.

Cursors never.
DTS - only when needed and never to control.

cidr Ten Centuries Points: 1353 More actions · Answer 5

Thanks guys, I tried this previously but it wasn't working for me because the actual solution uses the IN statement for NAME as there's three values to be pulled through. I removed IN and used '=' for each value in the WHERE clause, works now.

Thanks