how to find a employee with multiple role in table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi friends
i have table called emp which has 2 columns

1. emp_name
2. role

what i want is, i want to find employees with role A and B in single SQl statement

I tried

SELECT * FROM EMP WHERE ROLE LIKE 'A' AND ROLE LIKE 'B'

but its not working, any idea how to do it
i wana avoid use of cursor.

thanks in advance

Posted 30-Oct-12 20:34pm

dhage.prashant01

Add a Solution

Comments

bbirajdar 31-Oct-12 2:57am

Table with 2 columns and no primary-foreign keys?

dhage.prashant01 31-Oct-12 3:04am

Table has many columns
But i cut down to short as per my need :)
For the purpose of understanding

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2012-10-30T21:56:00

Solution 3

Use OR or IN in where statement

SQL

SELECT * FROM EMP WHERE ROLE LIKE 'A' OR ROLE LIKE 'B'

OR

SQL

SELECT * FROM EMP WHERE ROLE In ('A','B')

For finding employees who have both role then fire this query

SQL

SELECT EmpId FROM EMP WHERE ROLE LIKE 'A' OR ROLE LIKE 'B' Group by EMPId Having Count(EMPID)>1

Happy Coding!
:)

Posted 30-Oct-12 21:56pm

Aarti Meswania

Updated 30-Oct-12 22:06pm

v2

Comments

damodara naidu betha 31-Oct-12 7:36am

5+

Aarti Meswania 31-Oct-12 7:36am

thank you! :)

Om Prakash Pant · Answer 2 · 2012-10-30T20:43:00

Solution 1

SELECT * FROM EMP WHERE ROLE LIKE 'A' OR ROLE LIKE 'B'

OR

SELECT * FROM EMP WHERE ROLE LIKE 'A'
union
SELECT * FROM EMP WHERE ROLE LIKE 'B'

Posted 30-Oct-12 20:43pm

Om Prakash Pant

Comments

dhage.prashant01 31-Oct-12 3:06am

I want to find employee with both role A and B
SELECT * FROM EMP WHERE ROLE LIKE 'A' OR ROLE LIKE 'B'
will get records for employee whose role is either A or B, which is not my requirment

bbirajdar · Answer 3 · 2012-10-30T20:57:00

Solution 2

SQL

SELECT * FROM EMP GROUP BY EMP_NAME HAVING COUNT(ROLE) >1

Posted 30-Oct-12 20:57pm

bbirajdar