CODEWITHSUNDEEP
sqlsql-server
kr153
kr153

Reputation: 282

get record having a combination of columns

this is my sample table

CREATE TABLE agentskills 
( 
    id          INT PRIMARY KEY NOT NULL IDENTITY, 
    skilluserid INT, 
    skillid     INT NOT NULL, 
    skilltypeid INT, 
    priority    INT DEFAULT 0 
)

these are my sample data

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (1,21,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (1,22,1,1)

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (2,23,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (2,24,1,1)

INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (3,21,1,1)
INSERT INTO AGENTSKILLS (skilluserid , skillid, SKILLTYPEID,priority ) VALUES (3,22,1,1)

Here, 'skilluserid' is the id of user having the skill 'skillid'

how can i fetch users/skilluserid having a combination of skillid ?

eg : get user(s) having skillid 21 and 22 or 22 and 21 or 21,22,23. any combination having 21 and 22.

Upvotes: 4

Views: 60

Answers (3)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You can use intersect,

select skilluserid
from agentskills 
where skillid =21

INTERSECT

select skilluserid
from agentskills 
where skillid =22

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you could use exist if you need all column in selection

select t1.* from agentskills t1
where  exists ( select 1 from agentskills t2 where t1.skilluserid=t2.skilluserid
                 and skillid in (21, 22) 
                 having count(distinct skillid) = 2
            ) and skillid in (21, 22)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use aggregation and having:

select skilluserid
from agentskills
where skillid in (21, 22)
group by skilluserid
having count(distinct skillid) = 2;  -- "2" = size of list in where clause

Upvotes: 3

Related Questions