Use comma separated values in where clause and compare it with in clause

This is the edited question with full problem. Following is the table structure. (Only necessary columns are shown below.)

Table Name: tblQualificationMaster.

Qualiid  QualiName
-------  ---------
1        S.S.C  
2        H.S.C  
3        B.Sc  
4        M.C.A  
5        M.Sc(IT)  
6        B.E  
7        M.B.A  
8        B.Com  
9        M.E  
10       C.S  
12       M.Com

Table Name: tblAppResumeMaster.

AppId  FirstName  LastName  TotalExpYears  TotalExpMonths  
-----  ---------  --------  -------------  --------------
1      Rahul      Patel     7              0  
2      Ritesh     Shah      0              0  
3      Ajay       shah      7              6  
4      Ram        Prasad    7              6  
5      Mohan      Varma     5              0  
6      Gaurav     Kumar     8              0  

Table Name: tblAppQualificationDetail. (For better reading I am writing comma separated value for all rows except first row but in my database all values are stored like for appid=1. i.e one row for each qualificationid.)

Appid  QualiId  
-----  -------
1      1  
1      2  
1      3  
1      4  
2      1,2,3  
3      1,2,6  
4      1,2,3,5  
5      1,2,3,4  
6      1,2,6,9  

Table Name: tblVacancyMaster

VacId  Title           Criteria  Req.Exp  KeySkills
-----  --------------  --------  -------  ---------------
1      Programmer      4,5,6    开发者_如何学编程 4        .net,java,php
2      TL              4,5       3        .net,java,php
3      Project Mngr.   4,6,9     4        .net,java,php,sql
4      Java Developer  4,5,6     0        java,oracle,sql
5      Manager         7,9       7        bussiness management
6      Supervisior     3,8       3        marketing
7      PHP Developer   4,5       0        php,mysql,send

Now based on this detail I want to create view which should have following fields. (It is shown for VacId=1 but I need this for all vacancies so that I can fire where clause on this view like select * from view where VacId=3.)

AppId  FirstName  LastName  QualiName  QualiId  TotalExp  VacId  VacTitle
-----  ---------  --------  ---------  -------  --------  -----  ----------
1      Rahul      Patel     M.C.A      4        7         1      Programmer
3      Ajay       Shah      B.E.       6        7         1      Programmer
5      Mohan      Verma     M.C.A      4        5         1      Programmer
6      Gaurav     Kumar     B.E        6        8         1      Programmer
6      Gaurav     Kumar     M.E        9        8         1      Programmer

This view shows AppId 1,3,5 and 6 are eligible for vacancy 3 but it shows duplicate entry for app 6. How can I get unique records?

I may be wrong in database design because this is my first project and I am learning database, so let me know and correct if something goes against database standards.

My previous query (Note: Earlier I was using one intermediate table tblVacancyCriteriaDetail which was having columns VacId and QualiId and my table tblVacancyMaster was not having column criteria)

select
  ARM.AppId,
  ARM.AppFirstName,
  ARM.AppLastName,
  ARM.AppMobileNo,
  AQD.QualiId,
  VacQualiDetail.QualiName,
  ARM.AppEmailId1,
  VacQualiDetail.VacID,
  ARM.TotalExpYear,
  VacQualiDetail.VacTitle,
  VacQualiDetail.DeptId,
  VacQualiDetail.CompId,
  CM.CompName  
from
  tblAppResumeMaster ARM,
  tblAppQualificationDetail AQD,
  tblCompanyMaster CM,
  (
    select
      VM.VacID,
      VM.VacTitle,
      VM.CompId,
      VM.DeptId,
      vcd.QualificationID,
      QM.QualiName,
      VM.RequiredExperience as Expe
    from
      tblVacancyCriteriaDetail VCD,
      tblVacancyMaster VM,
      tblQualificationMaster QM
    where VCD.VacID=VM.VacID
      and VCD.QualificationID=QM.QualificationId
      and VM.Status=0
  ) as VacQualiDetail
where AQD.AppId=arm.AppId
  and aqd.QualiId=VacQualiDetail.QualificationID
  and ARM.TotalExpYear>=Expe
  and cm.CompId=VacQualiDetail.CompId

create view vAppList as
select AppId, 
       FirstName, 
       LastName, 
       QualiName, 
       Qualiid, 
       TotalExpYears, 
       VacId, 
       Title
from (select ARM.AppId,
             ARM.FirstName,
             ARM.LastName,
             QM.QualiName,
             QM.Qualiid,
             ARM.TotalExpYears,
             VM.VacId,
             VM.Title,
             row_number() over(partition by ARM.AppId, VM.VacId order by QM.Qualiid) as rn
      from tblAppResumeMaster as ARM
        inner join tblAppQualificationDetail as AQD
          on ARM.AppId = AQD.Appid
        inner join tblQualificationMaster as QM
          on AQD.QualiId = QM.Qualiid  
        inner join tblVacancyMaster as VM
          on  ','+VM.Criteria+',' like '%,'+cast(QM.Qualiid as varchar(10))+',%'    
     ) as V
where V.rn = 1

The sub query will have duplicate when one applicant matches more then one qualification. In that case QualiName will have the value for the lowest Qualiid.

If you go back to use tblVacancyCriteriaDetail, which I think you should, the view would look like this.

create view vAppList as
select AppId, 
       FirstName, 
       LastName, 
       QualiName, 
       Qualiid, 
       TotalExpYears, 
       VacId, 
       Title
from (select ARM.AppId,
             ARM.FirstName,
             ARM.LastName,
             QM.QualiName,
             QM.Qualiid,
             ARM.TotalExpYears,
             VM.VacId,
             VM.Title,
             row_number() over(partition by ARM.AppId, VM.VacId order by QM.Qualiid) as rn
      from tblAppResumeMaster as ARM
        inner join tblAppQualificationDetail as AQD
          on ARM.AppId = AQD.Appid
        inner join tblQualificationMaster as QM
          on AQD.QualiId = QM.Qualiid
        inner join tblVacancyCriteriaDetail as VCD    
          on QM.Qualiid = VCD.QualiID
        inner join tblVacancyMaster as VM
          on  VCD.VacId = VM.VacId
     ) as V
where V.rn = 1     

I have never worked with MS SQL Server, so I think the best way would be to use Regex (try to find something about it in SQL Server documentation).

But I think this should work:

select * from Table1 Where (',' + qualificationid + ',') like '%,6,%';

I assume that string concatenation is done using + sign.

revised:

create a new function:

CREATE FUNCTION Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
    declare @idx int     
    declare @slice varchar(8000)     

    select @idx = 1     
        if len(@String)<1 or @String is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end 
return     
end

then you could use my prev answer:

SELECT * FROM TableA WHERE ColumnID IN split(SELECT ColumnWithValues FROM TableB)

try using COALESCE function to get your rows in one column with comma separeted.this is a simple examle

 declare @QualIDs varchar(50)=''
 select  @QualIDs= COALESCE(@QualIDs+ ', ', '') + CAST(Qualiid AS varchar(50)))
 from tblQualificationMaster

this will return all Qualiid with comma separated you can use it in where clause or in sub query.

to read more about COALESCE go to http://msdn.microsoft.com/en-us/library/ms190349.aspx