Sql query Clarification | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Sql query Clarification

The query is pertaining to ver Sqlserver 7.0 but fms database db_cmptlevel is in
65. One of our colegue written the below query and we are not getting
the correct results with this
select distinct substring(cost_center,3,3)
from fms..gl_voucher_detail a(nolock), northwind..amar_brh_in_Cost_Center
c(nolock)
where a.company_code = ‘argrparb’
and cost_center like ‘m%’
and a.account_no like ‘4%’
and substring(cost_center,3,3) <> ltrim(rtrim(brhcode))
and accounting_date between ’01 apr 2003′ and ’30 apr 2003′
and tran_type != ‘mm’
If we write the same query like this we are getting the correct
output select distinct substring(cost_center,3,3)
from fms..gl_voucher_detail a(nolock)
where a.company_code = ‘argrparb’
and cost_center like ‘m%’
and a.account_no like ‘4%’
and accounting_date between ’01 apr 2003′ and ’30 apr 2003′
and tran_type != ‘mm’
and substring(cost_center,3,3) not in (select ltrim(rtrim(brhcode)) from
northwind..amar_brh_in_Cost_Center c(nolock)) Why the first query was not worked, and why we got the exact output from the
second query pl clarify. Rushendra
Take a close look at the first query and imagine how you could write that using ANSI SQL join syntax and get the desired results. Probably can’t do it can you? The problem is joining on only a != operator. When you do this, you are pretty much guaranteed to get a cross join. I would suggest just writing the query the way you have it in the second example, I think that is the only way to do it.
if you post the table scripts with primary key and unique index information, it is very simple to investigate the query.
let me reiterate that the primary keys and unique indexes are very important.
without that information, we can only guess at what’s going on
]]>