Looking for some help with UNION and GROUP BY

[katyD]

Good morning phpFreaks!

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,

 

[Muddy_Funster]

How about just selecting the min() or max() sortBy depending on your sort order (still grouping by cat_id)? this will then only return the lowest (or highest) sortBy value for that grouping.

[katyD]

Thanks Muddy!

I've tried running this:

 

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

HAVING MIN( sortBy )

 

just to see what it returns, but it seems to drop some of the rows (it returns 6 instead of 10).

 

If I run

 

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

HAVING MAX( sortBy )

 

it gives me the same results as the original query.

 

Lastly, if I try this:

 

SELECT MIN( sortBy ) , 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

 

It gives me different results in the MIN(sortBy) column than the sortBy column (which is good), but all the values in the other columns are the same.

 

Not sure if that sheds any light?

[Muddy_Funster]

It gives me different results in the MIN(sortBy) column than the sortBy column (which is good), but all the values in the other columns are the same.

Don't understand what your saying here. I assume it's producing incorrect results?

 

What about

SELECT sortBy , 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' 
AND sortBy IN (select MIN (sortBy) as firstPage FROM pages group by ID)
ORDER BY catID

 

That assumes that the ID column in pages is used to identify each user, not each page.  If it's not I'll need more accurate information about your tables.

 

[katyD]

Muddy,

I did try a bunch of different stuff, but no luck on this one. 

I've attached a simplified copy of the database, which I thought may help.

 

The problem part of the query is this bit:

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

 

If I remove the GROUP BY (and add an ORDER):

 

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' ORDER BY sortBy, catID

 

I can see that the webName for 'What's Happening' should be 'whats-happening' with a sortBy=0, but when I add the group back in, it is returning 'ScugogSportsHallofFame' with a sortBy=3

 

Does that make this any more clear?  Sorry, I know I'm not doing a great job of explaining.

18198_.zip

[katyD]

My apologies, I attached the wrong zip file, here is the correct one.  Did you ever have one of those days?

18205_.zip

[Oldergit]

Muddy,

I did try a bunch of different stuff, but no luck on this one. 

I've attached a simplified copy of the database, which I thought may help.

 

The problem part of the query is this bit:

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

 

If I remove the GROUP BY (and add an ORDER):

 

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' ORDER BY sortBy, catID

 

I can see that the webName for 'What's Happening' should be 'whats-happening' with a sortBy=0, but when I add the group back in, it is returning 'ScugogSportsHallofFame' with a sortBy=3

 

Does that make this any more clear?  Sorry, I know I'm not doing a great job of explaining.

 

Have you tried changing the JOINS to LEFT JOINS and the group by to GROUP BY pagesCats.catID

 

 

[katyD]

I have tried that, and am getting exactly the same results.

[katyD]

After msashing my head against a wall for days, 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.