Richard Cooper wrote:
>
> i.e. results for CP1 disappear when I uncomment out the either of the
> two and statements.
> 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
I think you need something like:
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.subPage = 'yes'
AND CP1.pageID <> CP2.pageID
AND CP2.isFloat = 'no')
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
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:259283
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
