SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Replace: COLUMN IN ('a','b'...) by RELATIONSHIP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 01/21/2013 :  13:03:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/21/2013 :  13:51:57  Show Profile  Reply with Quote
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
Go to Top of Page

barnabeck
Posting Yak Master

Spain
187 Posts

Posted - 01/22/2013 :  12:14:17  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/22/2013 :  13:13:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000