September 27, 2007 at 10:06 am
I have the following 3 tables:
ProductClasses (PK - ProductClassID)
ProductObjects (PK - ProdnodeID, ProductClassID)
ProductRelationships (PK - ParentProdNodeID, ChildProdNodeID)
I need to display the ProductClassName from the ProductClasses table for the
ParentProdNodeID and ChildProdNodeID from ProductRelationships. The
following query returns the ProductClassName for the ParentProdNodeID. My
question is how do I return the ProductClassName for the ChildProdNodeID as
well in the same query.
select pr.parentprodnodeid, pr.childprodnodeid, po.prodnodeid,
pc.sysclassname as [Parent Class], pc.sysclassname as [Child Class]
from productrelationships pr
join productobjects po
on pr.parentprodnodeid = po.prodnodeid
join productclasses pc
on po.prodclassid = pc.prodclassid
Thanks in advance.
September 27, 2007 at 10:23 am
Include another set of joins...
SELECT
pr.parentprodnodeid
,pr.childprodnodeid
,po.prodnodeid
,pc.sysclassname AS [Parent Class]
,cc.sysclassname AS [Child Class]
FROM
productrelationships pr
LEFT JOIN productobjects po
ON pr.parentprodnodeid = po.prodnodeid
LEFT JOIN productclasses pc
ON po.prodclassid = pc.prodclassid
LEFT JOIN productobjects co
ON pr.childprodnodeid = co.prodnodeid
LEFT JOIN productclasses cc
ON co.prodclassid = cc.prodclassid
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 27, 2007 at 10:24 am
....but something tells me there's an easier way.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 27, 2007 at 10:42 am
You can use inner join instead of left join if both ParentNodeId and ChildNodeId are non-nullable
select
pr.parentprodnodeid,
pr.childprodnodeid,
po.prodnodeid as [Parent Node],
co.prodnodeid as [Child Node],
pc.sysclassname as [Parent Class],
cc.sysclassname as [Child Class]
from productrelationships pr
join productobjects po on pr.parentprodnodeid=po.prodnodeid
join productclasses pc on po.prodclassid=pc.prodclassid
join productobjects co on co.prodnodeid=pr.ChildProdNodeID
join productclasses cc on cc.prodclassid=co.prodclassid
September 27, 2007 at 10:44 am
Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply