CODEWITHSUNDEEP
mysql
lahiru
lahiru

Reputation: 15

how to select from a table which has two different values in a column?

========================================================

this is the sample db

I just want to get user who has both 2 and 14 in skills column. The answer should be "2" enter image description here

Upvotes: 0

Views: 35

Answers (3)

Torrezzzz
Torrezzzz

Reputation: 307

use this: 

select seekerID from table_name where skillid="2" and seekerID = ( select author from table_name where skillid="14")

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT seekerID
FROM mytable
WHERE skillID IN (2, 14)
GROUP BY seekerID
HAVING COUNT(DISTINCT skillID) = 2

DISTINCT keyword is necessary only in case skillID values can occur multiple times for a single seekerID.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The easiest way to do this would be

select seekerID, count(*) as cnt
from table_name
where skillid in (2,14)
group by seekerID
having cnt = 2

Upvotes: 0

Related Questions