SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filter rows based on two columns


Filter rows based on two columns

Author
Message
cidr
cidr
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 263
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18660 Visits: 20451
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
nigelrivett
nigelrivett
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 211
where (name = 'Paul' and position = 'manager')
or name = 'Jim'


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

Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18660 Visits: 20451
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
nigelrivett
nigelrivett
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 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.

cidr
cidr
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 263
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search