Replace: COLUMN IN ('a','b'...) by RELATIONSHIP

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

General SQL Server Forums

New to SQL Server Programming

Replace: COLUMN IN ('a','b'...) by RELATIONSHIP

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-01-21 : 13:03:42

I have this portion of a query that I would like to simplify:

select PRODROUTE.PRODID,..., ColorCoding.TAG
from 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.TAG
from 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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-21 : 13:51:57

[code]Select PRODROUTE.PRODID,..., ColorCoding.TAG
from PRODROUTE
left outer join PRODTABLE on PRODROUTE.PRODID = PRODTABLE.PRODID
left outer join PRODBOM on PRODROUTE.PRODID = PRODBOM.PRODID
left outer join ColorCoding on PRODBOM.ITEMID = ColorCoding.articulo
Where ColorCoding.articulo IS NOT NULL[/code]

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-01-22 : 12:14:17

This is not the same! This query doesn't return any records in case that ColorCoding.articulo IS NOT NULL; and actually I'm most interested in these records where ColorCoding.articulo IS NULL!
the where clause just adds a condition to the relationship, not the whole query.

Martin

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 13:13:36

Can you try this (or the LEFT JOIN within the brackets changed to INNER JOIN depending on what you are trying to get)

SELECT PRODROUTE.PRODID,
       ...,
       ColorCoding.TAG     
FROM   PRODROUTE
       LEFT OUTER JOIN PRODTABLE
            ON  PRODROUTE.PRODID = PRODTABLE.PRODID
       LEFT JOIN
		(
			PRODBOM
			LEFT JOIN ColorCoding 
			ON PRODBOM.ITEMID = ColorCoding.articulo
		) ON PRODROUTE.PRODID = PRODBOM.PRODID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources