WHERE clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE clause

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-23 : 08:05:09

What's wrong?

where I say:
and centre_no = 'DV066'

why do I get 72,008 rows?

The row IS there. Why can't I eliminate the rest? The most basic filter. The 0 is a 0. A zero. Is it the joins?

select distinct
centre_no,
centre_name,
det_year,
det_quantity,
det_type,
det_unit_price det_money,
prcode,
stkcode,
stk_baseprice Stock_money,
pr_price Price_money,
pr_sort_key Price_Category,
stk_sort_key Stock_Category,
pr_usrchar1 Price_net,
stk_sort_key3 Stock_net,
st_nett,
nl_cat_code4,
nl_categoryname
from
financetemplate.dbo.sl_pl_nl_detail det
left join financetemplate.dbo.stk_stock s on stkcode = det.det_stock_code
left join financetemplate.dbo.prc_price_recs p on prcode = det.det_price_code
inner join financetemplate.dbo.sl_accounts sla on sla.cucode = det.det_account
inner join asdandat.dbo.tbl_centre c on c.centre_no = sla.cucode
inner join financetemplate.dbo.sl_transactions t on det.det_header_ref = t.st_header_ref
inner join financetemplate.dbo.nl_category nlc on right(det.det_analysis, 3) = nlc.nl_cat_code4
left join financetemplate.dbo.prc_price_recs2 p2 on p2.prcode2 = p.prcode
where c.reg_status = 'R'
and centre_no = 'DV066'
and det_year = 'L'
and prcode = 'ASWB01'
and det_quantity > 5
and pr_sort_key in ('REGISTRATIONS', 'CANDIDATE FEES') or stk_sort_key in ('STUDENT BOOKS', 'RESOURCE BOOKLETS')
and pr_sort_key is not null or stk_sort_key is not null
order by centre_no, pr_sort_key, stk_sort_key

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-06-23 : 08:22:22

the OR conditions in your WHERE clause is causing this, you need to put proper paranthesis around them.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-23 : 08:22:31

try this



select distinct
centre_no,
centre_name,
det_year,
det_quantity,
det_type,
det_unit_price det_money,
prcode,
stkcode,
stk_baseprice Stock_money,
pr_price Price_money,
pr_sort_key Price_Category,
stk_sort_key Stock_Category,
pr_usrchar1 Price_net,
stk_sort_key3 Stock_net,
st_nett,
nl_cat_code4,
nl_categoryname
from
asdandat.dbo.tbl_centre c 
inner join financetemplate.dbo.sl_accounts sla on sla.cucode = c.centre_no
inner join financetemplate.dbo.sl_pl_nl_detail det on sla.cucode = det.det_account
left join financetemplate.dbo.stk_stock s on stkcode = det.det_stock_code
left join financetemplate.dbo.prc_price_recs p on prcode = det.det_price_code and p.prcode = 'ASWB01'
inner join financetemplate.dbo.sl_transactions t on det.det_header_ref = t.st_header_ref
inner join financetemplate.dbo.nl_category nlc on right(det.det_analysis, 3) = nlc.nl_cat_code4
left join financetemplate.dbo.prc_price_recs2 p2 on p2.prcode2 = p.prcode
where c.reg_status = 'R'
and centre_no = 'DV066'
and det_year = 'L'
and det_quantity > 5
and (pr_sort_key in ('REGISTRATIONS', 'CANDIDATE FEES') or stk_sort_key in ('STUDENT BOOKS', 'RESOURCE BOOKLETS'))
order by centre_no, pr_sort_key, stk_sort_key

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:41:52

This doesn't seem right.
Only last three characters for a column named Code4?

inner join financetemplate.dbo.nl_category nlc on right(det.det_analysis, 3) = nlc.nl_cat_code4
inner join financetemplate.dbo.nl_category nlc on right(det.det_analysis, 4) = nlc.nl_cat_code4

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources