November 24, 2008 at 5:04 am
All,
I need your help...
I have a code which has a inner join check for a particular field (say rollup) between two tables. Now an additional check has to be performed between these tables, that is, apart from the normal inner join, i have to check whether the particular field (rollup) which has the value B should have a look up with another field ( say territory = 07) taken from the same table.. This check should work in addition with the existing joins condition.
Part of the current code which explains this part is given below:
Select Premium_Access.locsta, Premium_Access.territory,
Premium_Access.rollup, Premium_Access.prempct,
Premium_Access.rollupnm
from (Premium_Access
inner join Premium
on Premium_Access.rollup = Premium.rollup)
where Premium_Access.locsta ='al'
group by
Premium_Access.locsta,
Premium_Access.territory,
Premium_Access.rollup,
Premium_Access.prempct,
Premium_Access.rollupnm
go
I have modified the where clause to look like this and the results are good, could anyone tell me if there is a better way to do it...
Select Premium_Access.locsta, Premium_Access.territory,
Premium_Access.rollup, Premium_Access.prempct,
Premium_Access.rollupnm
from (Premium_Access
inner join Premium
on Premium_Access.rollup = Premium.rollup)
where (Premium_Access.rollup = '111' and Premium_Access.territory = '07' and Premium_Access.locsta='al')
or Premium_Access.rollup = Premium.rollup
and Premium_Access.locsta ='al'
group by
Premium_Access.locsta,
Premium_Access.territory,
Premium_Access.rollup,
Premium_Access.prempct,
Premium_Access.rollupnm
go
November 24, 2008 at 6:30 am
Actually, it looks like you've changed the structure of the query unnecessarily.
...from (Premium_Access
inner join Premium
on Premium_Access.rollup = Premium.rollup)
where (Premium_Access.rollup = '111' and Premium_Access.territory = '07' and Premium_Access.locsta='al')
or Premium_Access.rollup = Premium.rollup
and Premium_Access.locsta ='al'
...
That OR clause kind of changes what you're doing and will affect performance and the use of indexes. You shouldn't have to repeat those criteria since you already defined them in the JOIN. You ought to be able to do this:
...
FROM Premium_Access pa
INNER JOIN Premium p
ON pa.Rollup = p.Rollup
WHERE
pa.rollup = '111'
AND pa.territory = '07'
AND pa.locsta = 'al'...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 24, 2008 at 6:49 am
Thanks for the prompt reply...
But i have a question arising from this solution, that is,
going by the logic u have explained, wouldn't the query fetch records having only rollup = 111 and territory = 07 and state = al. ??
November 24, 2008 at 7:02 am
Yes.
If you want records that only meet the criteria defined AND records that don't, it seems a bit odd. What you're saying then is return everything.
The two limiting factors, the join criteria and the 'AL' are in both result sets. If you say give me all where the join matches and the value is 'AL' that will automatically include any other filtering criteria, which eliminates the need for that second filter.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply