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)
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:
Instrument Type: Electric
then Instrument Type: Bass
then Instrument Type: Electric again
So it seems like I am missing some sub ordering or grouping.
MySQL Ordering Troubles
1 reply to this topic
Posted 24 May 2006 - 03:33 PM
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 DESCWell, 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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users