Hi folks,
I have a problem with a query where i don't know how to group the retrieved
records.
There are four tables involved in this query (hope you can ignore the german
declarations):
1. table: eintraege (the important one)
- id (counter)
- fzg_typ_id (integer)
- hersteller_id (integer)
- bereich_id (integer)
- kunden_id (integer) (not involved in query)
- datum (datetime)
- eintragstext (memo)
- preis (text)
2. table: fzg_typ
- fzg_typ_id (counter)
- name (text)
3. table: hersteller
- hersteller_id (counter)
- name (text)
- fzg_typ_id (text) (not involved, just for storing a list)
4. table: teilebereich
- bereich_id (counter)
- name (text)
- fzg_typ_id (text) (not involved, just for storing a list)
Now I have the following query which perfectly gives me the records I need:
<cfquery name="quicksearch" datasource="ersatzteile">
SELECT eintraege.id,
eintraege.fzg_typ_id,
eintraege.hersteller_id,
eintraege.bereich_id,
eintraege.datum,
eintraege.eintragstext,
eintraege.preis,
fzg_typ.name AS f_name,
hersteller.name AS h_name,
teilebereich.name AS t_name
FROM eintraege,
fzg_typ,
hersteller,
teilebereich
WHERE eintragstext LIKE '%#FORM.suchbegriff#%'
AND fzg_typ.fzg_typ_id = eintraege.fzg_typ_id
AND hersteller.hersteller_id = eintraege.hersteller_id
AND teilebereich.bereich_id = eintraege.bereich_id
ORDER BY fzg_typ.name,
hersteller.name,
teilebereich.name
</cfquery>
My problem is that I don't really want the retrieved records to be ordered
like stated in the ORDER BY-clause but to be grouped by these fields so that
the output could look like this:
{Heading} f_name1 / h_name3 / t_name9
{entry 1}
{entry 2}
{entry 3}
{Heading} f_name1 / h_name3 / t_name11
{entry 1}
{entry 2}
{Heading} f_name2 / h_name1 / t_name4
{entry 1}
{entry 2}
{entry 3}
{entry 4}
...
...
...
could anyone please point me to the right direction. I searched the web and
the Allaire docs now for almost a week and couldn't really find something
that works.
TIA
Patric
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
