asked on

Rewriting query for performance

I have a query .
table_1
clientid memberid denom num
a x 1 1
a y 1 1
b z 0 1

table_2
clientid memberid type
a x c
a y s
b z c

I need to select values where denom=1 and type='c' from the two tables. The denominator value should be sum(denom) where type='c' and denom=1.
denom from table_1 is a flag. I wrote a sub query here. But there are many fields like numerator1,numerator2,etc. So I wrote many subqueries . I dont want to use subqueries here as there is much data. Can anyone suggest me a query which is much efficient than subqueries
select clientid,denominator=(select sum(denom) from table_1 t1
join table_2 t2 on t1.clientid=t2.clientid
where t2.type='C' and denom=1)
from dbo.table_1 t1
join table_2 t2
on t1.clietid=t2.clientid
where t2.type='C'
group by e.lob

Anthony Perkins

Something like this:

SELECT  t1.clientid,
        SUM(t1.denom)
FROM    table_1 t1
        JOIN table_2 t2 ON t1.clientid = t2.clientid
WHERE   t2.type = 'C'
GROUP BY t1.clientid
HAVING  SUM(t1.denom) = 1

Open in new window

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial