MySQL Ordering Troubles

[dunksy]

Right, appealing to all you MySQL lovers:

I have three tables:

- lindoProducts, which stores the ID of the type of instrument it is in(instID)
- lindoInstruments, which in turn stores the ID of the type of category it is in (categoryID)
- lindoCategories

lindoInstruments and lindoCategories also have a field called ordering, so that I can order the categories on the page, and within these order the instruments, and then list products by name.


I am trying to do exactly this, but also allow for other selction criteria, for example selecting all products whose description contains the word "ultra".
I have this:

SELECT c.* FROM lindoProducts c, lindoInstruments p, lindoCategories q WHERE p.categoryID=q.ID AND c.instID=p.ID AND c.active='1' AND c.description LIKE 'ultra' ORDER BY q.ordering DESC

This seems to be working fine, but I am getting a very odd occurance.
For some reason, although it is ordering the categories fine, within these, it is not grouping the products by instrument type. So, I am getting:

Category: Guitars
Instrument Type: Electric
then Instrument Type: Bass
then Instrument Type: Electric again

So it seems like I am missing some sub ordering or grouping.

Any ideas?


Duncan

[fenway]

[code]SELECT c.*
FROM lindoProducts c
LEFT JOIN lindoInstruments p ON ( c.instID = p.ID )
LEFT JOIN lindoCategories q ON ( p.categoryID = q.ID )
WHERE c.active='1' AND c.description LIKE 'ultra'
ORDER BY q.ordering DESC[/code]
Well, no, this query won't do any grouping -- if any of these are one-to-many, you'll get them all. If you want to group by instrument type, you have to say so.