Heya! Thanks heaps for this. It wasn't exactly what was needed, but it got me pointed in the right direction and the whole thing is now chugging along very nicely.
You're probably right about the database not being optimised though... it's kinda in evolution, but since it's not mine I can't play with the structure of it too much. *sigh* Thanks again, *wanders off muttering "distinct, distinct" under her breath* Seona. Email: [EMAIL PROTECTED] Mobile: 0407 842 795 > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Taco Fleur > Sent: Sunday, 2 February 2003 2:31 PM > To: CFAussie Mailing List > Subject: [cfaussie] Re: Sorting data by category > > > Hi Seona, > > You would probably want something like this > > SELECT yourCategoryTable.category, yourCategoryTable.size, > yourWeaponTable.weapon > FROM yourCategoryTableINNER JOIN > yourWeaponTable ON yourCategoryTable.pk = > yourWeaponTable.fk > ORDER BY yourCategoryTable.category, yourCategoryTable.size > > I would suggest to do the query with a join instead of looping over the > data. > To select distinct items, use the word DISTINCT, i.e. SELECT DISCTINCT > [columns] > > Also I think the layout of your db is not optimised, but I could be wrong. > > After looking at your db structure again I believe you actually need > > SELECT id, category, size > FROM weapons > WHERE (category IN > (SELECT DISTINCT category > FROM weapons)) > ORDER BY category DESC, size > > If this is not what you are after let me know and I'll give another go... > > ----- Original Message ----- > From: "Seona Bellamy" <[EMAIL PROTECTED]> > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > Sent: Sunday, February 02, 2003 11:46 AM > Subject: [cfaussie] Sorting data by category > > > > OK, feeling braindead today, which is probably not the best time to be > > messing around with this stuff but I'm trying to feel like I'm doing > > something useful. ;) > > > > I have a whole heap of data I want to display in a table. First of all I > > need to sort it by category, then by size. So I have a query to > select the > > categories from the table, and I loop over this. Inside the loop I have > > another query which pulls out the data and orders it by size. > Then I do a > > <cfoutput> to loop over the query with the group function using size. > > > > Does this sound good in theory? > > > > Here's the practice: > > The first query selects every instance to each category. So > it's a helluva > > list, and contains heaps of duplicates. Is there a way to only > select each > > value once? I'd thought there was, but I can't find it anywhere. > > The second problem may or may not be related, but the data displayed by > the > > second query only has one value for each size, even when I know > for a fact > > that there is more than one item of that size in that category. > Now, maybe > > when I get my category query playing nicely this problem will > go away, but > I > > don't know. Is there anything I'm doing wrong? My code is > posted below for > > anyone who wants to try and shed some light on this. > > > > Thanks in advance, > > > > Seona. > > > > Email: [EMAIL PROTECTED] > > Mobile: 0407 842 795 > > > > <cfquery name="q_CategoryList" datasource="#request.dsn#"> > > SELECT Category > > FROM Weapons > > ORDER BY Category DESC > > </cfquery> > > > > <table> > > <cfloop query="q_CategoryList"> > > <cfquery name="q_WeaponSubList" datasource="#request.dsn#"> > > SELECT ID, Name, Size > > FROM Weapons > > WHERE Category = '#q_CategoryList.Category#' > > ORDER BY Size > > </cfquery> > > <tr> > > <td valign="top"> > > <cfoutput> > > <b>#q_CategoryList.Category#</b> > > </cfoutput> > > </td> > > <td> > > <table width="100%"> > > <cfoutput query="q_WeaponSubList" group="Size"> > > <tr> > > <td> > > #q_WeaponSubList.Size# > > </td> > > <td> > > <table width="100%"> > > <tr> > > <td width="50%" valign="top"> > > #q_WeaponSubList.Name# > > </td> > > <td> > > <A href="gm_Weapons.cfm?action=Edit&ID=#q_WeaponSubList.ID#">Edit</a> > > | <a > href="gm_Weapons.cfm?action=Delete&ID=#q_WeaponSubList.ID#">Delete</a> > > </td> > > </tr> > > </table> > > </td> > > </tr> > > </cfoutput> > > <tr> > > <td> </td> > > </tr> > > </table> > > </td> > > </tr> > > </cfloop> > > </table> > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003 > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > > > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003 > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003 --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
