Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter rows based on two columns Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 3:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:52 AM
Points: 74, Visits: 248
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
Post #1395010
Posted Tuesday, December 11, 2012 3:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
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'




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1395015
Posted Tuesday, December 11, 2012 3:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
where (name = 'Paul' and position = 'manager')
or name = 'Jim'



Cursors never.
DTS - only when needed and never to control.
Post #1395017
Posted Tuesday, December 11, 2012 4:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,180, Visits: 12,033
nigelrivett (12/11/2012)
where (name = 'Paul' and position = 'manager')
or name = 'Jim'


?? looks remarkably like a lowercase version of my solution



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1395025
Posted Tuesday, December 11, 2012 4:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
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.
Post #1395029
Posted Thursday, December 13, 2012 6:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:52 AM
Points: 74, Visits: 248
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
Post #1396163
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse