Hi All,
I've a self joining peice of SQL which is behaving oddly. The query I've set up
has a left outer join. Some of the query for this left join is affecting the
result set of the table it's joining to
i.e. results for CP1 disappear when I uncomment out the either of the two and
statements.
Does anyone know what I'm doing wrong. Code below:
<cfquery name="getAllCorp" datasource="#REQUEST.theDatabase#">
SELECT CP1.pageID
,CP1.pageGroupID
,CP1.subGroupID
,CP1.pageTitle
,CP1.alternativeLink
,CP1.linkTitle
,CP2.pageID as pageID2
,CP2.pageGroupID as pageGroupID2
,CP2.subGroupID as subGroupID2
,CP2.pageTitle as pageTitle2
,CP2.alternativeLink as alternativeLink2
,CP2.linkTitle as linkTitle2
FROM corpPages as CP1
LEFT OUTER JOIN
corpPages as CP2
ON CP1.pageGroupID = CP2.subGroupID
AND CP2.isFloat = 'no'
<!---AND CP2.subPage = 'yes'
AND CP1.pageID <> CP2.pageID--->
WHERE CP1.active = '1'
AND CP1.subPage = 'no'
AND CP1.isFloat = 'no'
AND CP1.satelliteID = '#satelliteID#'
AND CP1.navlocation = 'left'
ORDER BY
CP1.Priority
,CP2.Priority
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259281
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
