Get Records Having Both Values In "in" Using Sql

I need a SQL statement to extract those applicants from the database who have both the attachment tags. The belowq statement works fine if I want aaplicant who have either 7 or 8 a

Solution 1:

You need to do below, rather than distinct:

GROUPBY a.id HAVINGCOUNT(a.id) =2

Solution 2:

You could join to the ApplicationAttachment table twice to ensure that both tags are present. That way you would avoid the need for DISTINCT and GROUP BY.

SELECT a.id
FROM applicant a
JOIN applicantdeployment ad ON a.id = ad.applicantid
JOIN ApplicantAttachment at7 ON a.id = at7.applicantid AND at7.tag = 7JOIN ApplicantAttachment at8 ON a.id = at8.applicantid AND at8.tag = 8WHERE a.applicanttype = 'TCN/LN' AND ad.groundlocation IN (4,8,14)
AND ad.deploymentstatus = 1AND ad.trainingdeploymentstatus = 6

Solution 3:

In your sample data, shouldn't 7451 also be selected? You'll need to modify the code below to fit your query, but try out the following:

CREATETABLE #Temp
(
    ID INT,
    Tag INT
)

INSERTINTO #Temp
(
    ID,
    Tag
)
SELECT7328, 8UNIONSELECT7332, 8UNIONSELECT7332, 7UNIONSELECT7337, 7UNIONSELECT7449, 8UNIONSELECT7449, 7UNIONSELECT7451, 8UNIONSELECT7453, 7UNIONSELECT7451, 7selectdistinct(ad.id) from #Temp ad
join #Temp aton ad.id = at.id
AND ad.Tag != at.Tag
where at.tag in (7,8)
AND ad.Tag IN (7, 8)

Solution 4:

If you put the IN list items into a table called #Tags, this will work, and there won't be any need to rewrite the query if the number of tags changes.

selectdistinct(#Temp.ID)
from #Temp
wherenotexists (
  select*from #Tags
  wherenotexists (
    select*from #Temp as TempCopy
    where TempCopy.Tag = #Tags.Tag
    and TempCopy.ID = #Temp.ID
  )
)  

In English, this selects those IDs for which none of the required tags is missing.

Solution 5:

Try:

selectdistinct(a.id)
from applicant a
join applicantdeployment ad on a.id = ad.applicantid
join (select applicantid from applicantattachment at1
join applicantattachment at2 on at1.applicantid=at.applicantid
where at1.tag=7and at2.tag=8) at
on a.id = at.applicantid
where a.applicanttype = 'TCN/LN'and ad.groundlocation in (4,8,14)
and ad.deploymentstatus =1and ad.trainingdeploymentstatus = 6

Join the applicantattachment table to itself to get only the relevant applicantids.