Toby
Something like this will get you n rows per poem where n
is the number of options, filtering out the hidden ones.
Assuming each poem must have options - otherwise you need
an outer join.
select p.poem_id, p.poem_title, d.option_name, o.setting
from poem p, poem_option o, poem_option_def d
where p.poem_id = o.poem_id
and d.option_id = o.option_id
and poem_id in
(select p.poem_id from poem p, poem_option o, poem_option_def d
where p.poem_id = o.poem_id
and d.option_id = o.option_id
and d.option_name = 'hidden'
and d.setting = 'off')
You could use <cfoutput group="poem_id"> in CF
to "group" the output, but actually you want everything
on one line, so I think you'll have to do something
along the lines of
<cfset current_id = "">
<cfoutput query=...>
<cfif poem_id is not current_id>
<!--- New poem, so new line & poem id/title--->
<br>
#poem_id# #poem_title#
<cfif>
<!--- option details --->
#option_name#=#setting#
<current_id = #poem_id#
</cfoutput>
I can't think of a way of doing headers like your example,
if you dont want to hardcode the options.
None of this tested I'm afraid ;-)
Nick
-----Original Message-----
From: Toby Tremayne [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 07, 2001 3:47 AM
To: CF-Talk
Subject: curly queries
I've got a tricky problem that I just can't figure out.
I'm outputting a list of poems fomr a database (just the titles.) Each poem
has several "options" such as hidden, hilite etc. As I output the titles in
a list, I need to be able to run conditional checks against each option (IE
if hilite is "on" then display markers next to the title.) The tables are
set up like so:
-------------------------
poem
-------------------------
poem_id numeric
poem_title varchar
-------------------------
-------------------------
poem_option
-------------------------
poem_id numeric
option_id numeric
setting varchar
-------------------------
-------------------------
poem_option_def
-------------------------
option_id numeric
option_name varchar
-------------------------
you can see my problem (hopefully). To start with I'm getting each
poem_title repeated once for every option that exists. In the query I need
to select all this information for only the poems whose "hidden" option has
a setting of "off", then I need to output the title only once obviously, at
that time check each of it's options.
This doesn't quite work because while what I NEED is this:
one record:
---------------------------------------------------------------------
poem_id | poem_title | hidden | hilite | spellcheck |
---------------------------------------------------------------------
01 "my poem" "off" "on" "on"
instead, I have this:
one record:
---------------------------------------------------------
poem_id | poem_title | option_name | setting |
---------------------------------------------------------
01 "my poem" "hidden" "off"
01 "my poem" "spellcheck" "on"
01 "my poem" "hilite" "on"
as you can see this makes it difficult to do a <cfif hilite is "on"> <img
src="hiliter.gif"> </cfif>
can anyone help me with this please? i know it's going to be a combination
of writing the correct query and grouping the output - it's just eluding me
utterly. I can't / don't want to hardcode each option into the queries etc,
because obivously the number and names of options will evnetually grow...
help - anyone?
Toby Tremayne
Code Poet and Zen Master of the Heavy Sleep
Show Ads Interactive
359 Plummer St
Port Melbourne
VIC 3207
P +61 3 9245 1247
F +61 3 9646 9814
ICQ UIN 13107913
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
