CODEWITHSUNDEEP
sql-serversql-server-2008
Prashant16
Prashant16

Reputation: 1526

Search in one column multiple time

I am using SQL Server 2008.

This is my query output :

Reg_Id  |  ITSkillName  
----------------------
  118   |  JAVASCRIPT
  371   |  PHP
  371   |  ANDROID
  6170  |  PHP

Here, Reg_Id is foreign key. I want Reg_Id which have PHP and ANDROID skills.

So I have tried like

SELECT * 
FROM dbo.ITSkill AS is2 
WHERE ITSkillName = 'PHP' AND ITSkillName = 'ANDROID'

but it returns 0 records. Please help me. Thank you.

Upvotes: 1

Views: 66

Answers (5)

Kahn
Kahn

Reputation: 1660

As marc_s suggested, INTERSECT is a valid alternative. However, depending on how many various Skills there are, it can get kind of heavy since the query optimizer (in my experience at least) may choose less optimal ways to execute the query.

One other option at least, would be to use this:

SELECT s.*
FROM dbo.ITSkill s
JOIN 
   (SELECT Reg_ID
   FROM dbo.ITSkill AS is2 
   WHERE ITSkillName IN ('PHP','ANDROID')
   GROUP BY Reg_ID
   HAVING COUNT(DISTINCT ITSkillName) = 2) SRC
      ON SRC.Reg_ID = s.Reg_ID

Now obviously, you need to update the HAVING COUNT value to match the number of parameters in the IN clause. In code you may sometimes concatenate hundreds of INTERSECT queries, and that's where this model really starts to shine. At minimum it should be around equally effective performance-wise.

EDIT: Bah, just noticed damien had the same idea there! But this also has the join to get the results as per the OP so I'll leave it here. Give the creds and fortune to damien. ;)

Upvotes: 0

marc_s
marc_s

Reputation: 754438

Think in sets! What you're looking for is the intersection of two sets: those with PHP skills, and those with ANDROID skills. SQL has an INTERSECT operator:

SELECT Reg_ID FROM dbo.ITSkill
WHERE Skill = 'PHP'

INTERSECT

SELECT Reg_ID FROM dbo.ITSkill
WHERE Skill = 'ANDROID'

Upvotes: 3

Dan
Dan

Reputation: 4502

You need to JOIN the table to itself to get the results you want.

 SELECT A.Reg_Id
   FROM dbo.ITSkill A
   INNER JOIN dbo.ITSkill B ON A.Reg_Id = B.Reg_Id AND B.ITSkillName='PHP'
   WHERE A.ITSkillName='ANDROID'

If you need to match an arbitrary number of skills, but can't guarantee that Reg_ID, ITSkillName is unique, you can use a CTE to slightly modify Damien_The_Unbeliever's answer as follows:

   ;WITH UniqueSkills AS (SELECT DISTINCT Reg_ID, ITSkillName FROM dbo.ITSkill)
   SELECT Reg_ID,COUNT(*) FROM UniqueSkills AS is2
   WHERE ITSkillName IN(select Skill from @SearchSkills)
   GROUP BY Reg_ID
   HAVING COUNT(*) = (select COUNT(*) from @SearchSkills)

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

SELECT * FROM dbo.ITSkill AS is2 
WHERE ITSkillName = 'PHP' 
AND Reg_Id IN( SELECT DISTINCT Reg_Id 
                FROM dbo.ITSkill 
                WHERE ITSkillName = 'ANDROID')

Test

DECLARE @t TABLE (Reg_Id INT,ITSkillName VARCHAR(20))
INSERT INTO @t
VALUES 
  (118,'JAVASCRIPT'),
  (371,'PHP'),
  (371,'ANDROID'),
  (6170,'PHP')


SELECT * FROM @t AS is2 
WHERE ITSkillName = 'PHP' 
AND Reg_Id IN( SELECT DISTINCT Reg_Id 
                FROM @t 
                WHERE ITSkillName = 'ANDROID')

Result Set

Reg_Id  ITSkillName
371     PHP

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

This is a relational division problem. If the Reg_ID and ITSkillName columns, taken together, are unique in this table, then we can just do:

   SELECT Reg_ID,COUNT(*) FROM dbo.ITSkill AS is2
   WHERE ITSkillName IN('PHP','ANDROID')
   GROUP BY Reg_ID
   HAVING COUNT(*) = 2

This generally extends to more skills by adjusting the IN clause and the expected count.

I.e. if you have a table-valued parameter containing the skills to search for, you could have:

   SELECT Reg_ID,COUNT(*) FROM dbo.ITSkill AS is2
   WHERE ITSkillName IN(select Skill from @SearchSkills)
   GROUP BY Reg_ID
   HAVING COUNT(*) = (select COUNT(*) from @SearchSkills)

Where @SearchSkills is the table-valued parameter.

Upvotes: 3

Related Questions