CODEWITHSUNDEEP
sql-server-2005t-sql
Confused SQL User
Confused SQL User

Reputation: 5

How to combine the WHERE Clause in SQL query

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

Upvotes: 0

Views: 360

Answers (3)

user347594
user347594

Reputation: 1296

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

Upvotes: 0

devio
devio

Reputation: 37225

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.

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171589

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

Upvotes: 2

Related Questions