Use comma separated values ​​in where where and compare them to

Question

This is an edited question with a complete problem. Below is the table structure. (The following are only the required columns.)

Table Name: tblQualificationMaster .

 Qualiid QualiName ------- --------- 1 SSC 2 HSC 3 B.Sc 4 MCA 5 M.Sc(IT) 6 BE 7 MBA 8 B.Com 9 ME 10 CS 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 a better read, I write a comma-separated value for all lines except the first line, but in my database all values ​​are stored as for appid=1 Ie one line 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 a view that should have the following fields. (This is shown for VacId=1 , but I need it for all vacancies, so I can run where where on this view, for example select * from view where VacId=3 )

 AppId FirstName LastName QualiName QualiId TotalExp VacId VacTitle ----- --------- -------- --------- ------- -------- ----- ---------- 1 Rahul Patel MCA 4 7 1 Programmer 3 Ajay Shah BE 6 7 1 Programmer 5 Mohan Verma MCA 4 5 1 Programmer 6 Gaurav Kumar BE 6 8 1 Programmer 6 Gaurav Kumar ME 9 8 1 Programmer 

In this view, AppId 1,3,5 and 6 are eligible for vacancy 3, but it shows duplicate entries for application 6. How can I get unique entries?

I may be wrong in the design of the database, because this is my first project, and I am studying the database, so let me know and fix it if something goes against the standards of the database.

My previous query (Note: I used to use one staging table tblVacancyCriteriaDetail , which had VacId and QualiId , and my tblVacancyMaster table tblVacancyMaster not have 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 

Answer 1

 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 

A subquery will be duplicated if one applicant meets more than one qualification. In this case, QualiName will matter for the lowest Qualiid .

If you revert to using tblVacancyCriteriaDetail, which I think you need, the view will 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 

Answer 2

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

But I think this should work:

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

I assume that string concatenation is done using the + sign.

Answer 3

try using the COALESCE function to get your rows in the same separeted column. this is a simple example

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

this will return all Qualiid with a comma separated, you can use it in the where clause or in a subquery.

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

Answer 4

Audit:

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 can use my previous answer:

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