I have a sql that is as under:
SELECT ib.branch_no,
ib.on_hand,
p.weightedav,
p.item_code,
FROM physical p
INNER JOIN
item_branch as ib on p.item_code = ib.item_code
WHERE ib.on_hand <> 0
This SQL returns only those branch_no that have on_hand <> 0.
I am trying to get all the branch_nos irrespective of the on_hand field, but while still using the where on_hand clause.
Taking the on_hand clause away solves my problem, but gives me large amount of un-needed rows with 0's.
I am using SQL SERVER 2008 R2.
Thanks in advance for any guidance. Please apologize if I am missing any information.
--------------------------------SAMPLE RESULTSET --------------
This is the result of using on_hand<>0
branchno weighted_av item_code x_value y_value on_hand PhysicalOH
999 72.00 80S1211001 Ink/White 10 16 16
999 72.00 80S1211002 Ink/White 12 19 19
1 72.00 80S1211003 Ink/White 14 -1 17
17 72.00 80S1211003 Ink/White 14 1 17
999 72.00 80S1211003 Ink/White 14 17 17
1 72.00 80S1211004 Ink/White 16 -1 15
--------------------------------SAMPLE RESULTSET --------------
This is the result of NOT using the on_hand<>0 (SHOW ALL - NO FILTERING).
If you look closely this result set has extra branches i.e. Branch numbers 2 3 4 5. By adding the filter I lose these branches which I dont want to. I still want to retain them while I am using a filter.
branch_no weighted_av item_code x_value y_value on_hand PhysicalOH
999 72.00 80S1211001 Ink/White 10 16 16
999 72.00 80S1211002 Ink/White 12 19 19
1 72.00 80S1211003 Ink/White 14 -1 17
17 72.00 80S1211003 Ink/White 14 1 17
999 72.00 80S1211003 Ink/White 14 17 17
999 72.00 80S1211003 Ink/White 14 -1 17
2 72.00 80S1211003 Ink/White 14 0 17
3 72.00 80S1211003 Ink/White 14 0 17
4 72.00 80S1211003 Ink/White 14 0 17
5 72.00 80S1211003 Ink/White 14 0 17
1 72.00 80S1211004 Ink/White 16 -1 15
Running without a filter results in 300K rows which crashes reporting services.
------------------------------------------ENTIRE SQL QUERY ---------------------------------------------
select
ib.branch_no,
p.weighted_av,
p.item_code,
p.x_value,
p.y_value,
ib.on_hand,
p.on_hand as PhysicalOH,
ip.price,
i.item_code as StyleCode,
i.description,
i.cat1,
i.cat2,
i.cat3,
i.cat4,
np.is_style_yn,
si.supplier_code ,
ysv.sort as YSort
from physical as p
left outer JOIN
item_branch as ib on p.item_code = ib.item_code
INNER JOIN
item_price as ip on p.item_code = ip.item_code and ip.price_type = 'P1'
INNER JOIN
style_values as sv on p.style_code = sv.style_code and p.x_value = sv.value
INNER JOIN
style_values as ysv on p.style_code = ysv.style_code and p.y_value = ysv.value and ysv.axis = 'Y'
INNER JOIN
ITEM as i on p.style_code = i.item_code
INNER JOIN
NON_PHYSICAL as np ON i.item_code = np.item_code and np.is_style_yn = 1
INNER JOIN
supplier_item as si ON i.item_code = si.item_code and si.pref_supp_no = 1
where
sv.axis = 'X' and
i.item_code in
(SELECT ITEM.item_code
FROM ITEM
INNER JOIN
NON_PHYSICAL ON ITEM.item_code = NON_PHYSICAL.item_code
LEFT JOIN
supplier_item ON Item.item_code = supplier_item.item_code and pref_supp_no = 1
WHERE NON_PHYSICAL.is_style_yn = 1 and ITEM.cat1 = 'Verge Sportswear Ltd' )
order by
si.supplier_code,
i.cat4,
i.cat3,
i.cat2,
i.cat1,
sv.sort