on x=x
and y=y
or...
and...

quote:

---

Originally posted by mikebird

BUT what IF I did a left join on a table much earlier in a query and now I want to add a different criteria to the join which would be in terms of an inner join, really?

Not only this, but the extra additional table I made comes right at the end of the joins, of course. When I want to add another condition to an already-linked table, this is in the middle of the joins, and I assume the parser goes through the joins in order, and when it sees the new fields (added at the end) not recognised, this query is invalid. Tried moving this join to the end, but then its crucial join setup is missing in the middle!!

So, the question is: How do I add a new join condition to an existing, already linked table, later. Rewriting everything from scratch may be an option, but still bebuilding everything line-by-line may not work at all, with what I want to do, and is there a way to fiddle a solution with a quick fix?

newest join at the end. Want to add conditions to the second left join, and it's there, after the AND

error: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p2.pr_usrchar1" could not be bound.

This is query:

select Centre_Name, centre_no, p2.pr_usrchar1, p2.prcode2, pr_sort_key, stk_sort_key, stk_sort_key3, det_type, det_quantity, det_unit_price, det_year, st_nett, nl_cat_code4, nl_categoryname
from financetemplate.dbo.sl_pl_nl_detail det
inner join financetemplate.dbo.sl_accounts sla on sla.cucode = det.det_account -- taking 1 second, still 53,344 rows
inner join asdandat.dbo.tbl_centre c on sla.cucode = c.centre_no
inner join asdandat.dbo.tbl_net_centre nc on c.centre_id = nc.centre_ID -- after these two, 3 seconds, 176,399 rows
left join financetemplate.dbo.prc_price_recs p on p.prcode = det.det_price_code
left join financetemplate.dbo.stk_stock s on s.stkcode = det.det_stock_code and s.stk_sort_key3 = p2.pr_usrchar1
inner join financetemplate.dbo.sl_transactions t on det.det_header_ref = t.st_header_ref
inner join financetemplate.dbo.prc_price_recs2 p2 on p2.prcode2 = p.prcode
inner join financetemplate.dbo.nl_category nlc on right(det.det_analysis, 3) = nlc.nl_cat_code4
where c.reg_status = 'R'
and (pr_sort_key = 'CANDIDATE FEES'
or stk_sort_key in ('STUDENT BOOKS','RESOURCE BOOKLETS') -- These two lines should give Workbooks
and det.det_quantity > 5)
and ((nl_cat_code4 between '102' and '106' and pr_usrchar1 = 'A')
or (nl_cat_code4 between '141' and '146' and pr_usrchar1 = 'B')
or (nl_cat_code4 between '201' and '231' and pr_usrchar1 = 'E')
or (nl_cat_code4 = '701' and pr_usrchar1 = 'J')
or (nl_cat_code4 between '703' and '707' and pr_usrchar1 in ('Y','X','S','W'))
--or (nl_cat_code4 in ('302','303') and pr_usrchar1 = 'D')
or (nl_cat_code4 between '304' and '311' and pr_usrchar1 in ('F','P','H','I','G','M','L','K')))
order by pr_usrchar1, nl_categoryname
--and nl_cat_code4 != '™'
--and t.st_description = 'CANDIDATE REGISTRATIONS'

---