How to combine the WHERE Clause in SQL query

Question

How could I make this query better?

SELECT ClientID, BatchID, jobid, subjobid, count(clientid) as Total 
FROM data with(nolock) 
WHERE batchid in (select BatchID from data with(nolock) where lookupcode = '111111111111') 
and clientid in (select ClientID from data with(nolock) where lookupcode = '111111111111') 
and jobid in (select jobid from data with(nolock) where lookupcode = '111111111111') 
and subjobid in (select subjobid from data with(nolock) where lookupcode = '111111111111') 
and entrytype <> 'C'
and entrytype <> 'M'
group by clientid,BatchID, jobid, subjobid

Answer 1

select ClientID, BatchID, jobid, subjobid, count(clientid) as Total 
from data with (nolock) 
where lookupcode = '111111111111'
    and entrytype not in ('C', 'M')
group by clientid, BatchID, jobid, subjobid

Update:

select d2.ClientID, d2.BatchID, d2.jobid, d2.subjobid, count(*) as Total  
from data with (nolock)  d1
inner join data d2 on d1.batchjobid = d2.batchjobid 
    and d1.subjobid = d2.subjobid
where d1.lookupcode = '111111111111' 
    and d2.entrytype not in ('C', 'M') 
group by clientid, BatchID, jobid, subjobid 

Answer 2

Try a CTE:

WITH lookup AS (
    select ClientID, BatchID, JobID, SubJobID 
    from data with(nolock) 
    where lookupcode = '111111111111'
)
SELECT ClientID, BatchID, jobid, subjobid, count(clientid) as Total 
FROM data with(nolock) 
WHERE batchid in (select BatchID from lookup) 
and clientid in (select ClientID from lookup) 
and jobid in (select jobid from lookup) 
and subjobid in (select subjobid from lookup) 
and entrytype not in ('C', 'M')
group by clientid,BatchID, jobid, subjobid

Your original solution however does not implement the condition you state in the comments:

find all rows that have the same batch job and subjob

since you do not check for the combination of job and subjob.

If you need to combine some fields for exact matches, try a condition like this

WHERE EXISTS(
    SELECT 1 
    FROM lookup 
    WHERE lookup.jobid = data.jobid AND lookup.subjobid = data.subjobid
)

Since we don't have a table structure and sample data, it's hard for us to check whether our solutions are correct.

Answer 3

Please, try this.

SELECT d1.clientid, d1.BatchID, d1.jobid, d1.subjobid, count(clientid) as Total 
FROM data with(nolock) as d1
join (
    select BatchID, ClientID, jobid, subjobid 
    from data with(nolock) 
    where lookupcode = '111111111111'
) as d2
WHERE d1.batchid = d2.BatchID 
and d1.clientid = d2.clientid
and d1.jobid = d2.jobid
and d1.subjobid d2.subjobid
and d1.entrytype <> 'C'
and d1.entrytype <> 'M'
group by d1.clientid, d1.BatchID, d1.jobid, d1.subjobid