I have this portion of a query that I would like to simplify:select PRODROUTE.PRODID,..., ColorCoding.TAGfrom PRODROUTE left outer join PRODTABLE on PRODROUTE.PRODID = PRODTABLE.PRODID left outer join PRODBOM on PRODROUTE.PRODID = PRODBOM.PRODID and PRODBOM.ITEMID in ('0224596','12356388','12223549','12627543','12877230','1290304','14036913','14039654','14044560','7522936') left outer join ColorCoding on PRODBOM.ITEMID = ColorCoding.articulo
This query returns a list of Production orders. Now:- Every Production order is composed of several items (PRODBOM). - If among these items there is one that belongs to a special class (marked red), I want that Production order to carry a special marker (ColorCoding.TAG)The ColorCoding table assigns to every item of that class a hexadecimal-colorcode. The column TAG contains the html code that gives colors to the asp gridview cell.This is working…. But: I dislike that whenever I add a new item to that special class of items under control, I have to change the query. The class of critical items seems to be sufficiently described by the table ColorCoding and it would be much cooler to rather refer to that table and avoid the PRODBOM.ITEMID in ('0224596','…) expression!So I did:select PRODROUTE.PRODID,..., ColorCoding.TAGfrom PRODROUTE left outer join PRODTABLE on PRODROUTE.PRODID = PRODTABLE.PRODID left outer join PRODBOM on PRODROUTE.PRODID = PRODBOM.PRODID and ColorCoding.articulo IS NOT NULL left outer join ColorCoding on PRODBOM.ITEMID = ColorCoding.articulo
But ColorCoding.articulo can not be bound!How can I achieve this?Martin