|
Okay, with Old Pendant's help, I've finally got something which seems to be pretty close to what I was looking for? I've been looking at it too long to get into all the nit-picking details, but this is certainly much better than what I started with.
For anyone who is curious (or perhaps this will somehow help someone else), here is the query I've ended up with:
(
SELECT webName, pageName AS titleSort, pageName AS title, pages.ID, catID, pageName, sortBy
FROM pages
JOIN pagesIndex ON pages.ID = pagesIndex.pageID
WHERE catID = '2'
)
UNION (
SELECT webName, value AS titleSort, value AS title, pageID, X.catID, pageName, '0'
FROM pages, pagesCats, pagesIndex, (
SELECT catID, MIN( sortBy ) AS minsort
FROM pagesIndex, pages
WHERE pages.ID = pagesIndex.pageID
GROUP BY catID
) AS X
WHERE pagesIndex.catID = X.catID
AND pagesIndex.pageID = pages.ID
AND pages.sortBy = X.minsort
AND pagesCats.ID = X.catID
AND subsetof = '2'
GROUP BY catID
)
I'm sure it's a nightmare, but it seems to be doing the trick.
Thanks again for all your help.
|