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/
