Having problems with UNION and GROUP, help greatly appreciated
Good morning all!
I'm having an issue, and I think I know what my problem is, but don't know quite enough to solve it.
Basically, I have three tables:
pages (contains all the information to build a webpage, including ID, pageName which is the link text, webName which is the html address, and sortBy which lets them choose where page should fall, numerically)
pagesCats (contains a category ID, value which is the category name and subSetOf which specifies the parent category ID)
pagesIndex (contains a unique ID, a pageID (matches with pages.ID) and a catID (matches with pagesCats.ID)
I wrote this crazy query:
(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, pages.ID, catID, pageName, sortBy FROM pagesCats JOIN pagesIndex ON pagesCats.ID = pagesIndex.catID JOIN pages ON pagesIndex.pageID = pages.ID WHERE subsetOf = '2' GROUP BY catID ) ORDER BY pageName='Parent Category Name' DESC, sortBy, titleSort
which is supposed to:
- pull out the names of each of the categories, as well as get a link to the appropriate page
- pull out the name and web link to all the pages at the same level in the hierarchy
- sort the pages by anything which matches the category name, then by sortBy, then alphabetical
My problem is, that when I GROUP the pages which all belong to the same category, it just squishes them down, and I want the category to link to the first page which matches. I don't think you can sort a GROUP BY clause, and I'm probably using it wrong, but I can't figure out how to just get the top result from the query. I've been pulling my hair out over this for days, and just can't come up with anything better.
I'm using the call in a looping php function, so this one mySQL call can basically build the entire navigation of the site (about 500-odd pages). Other than the category names not linking to the right page, it's working brilliantly.
Does anyone know how I can solve this one? Any help would be massively appreciated. If you have any questions, or if there is anything I'm not explaining well, please let me know.
Thanks in advance,