Thanks for all of your answers. I tried a few of the suggestions but they
weren't quite right.
Bill Franklin's answer, while not complete, made me rethink what I was doing
from a different point of view. My error was that I was chaining all of the
record together as though I was trying to select data from the chained tables.
All I really cared about was that if it was not EVERYONE, that a poll X group X
student relationships existed. I only needed one record to prove that. The
solution, which works, is below:
<cfquery name="random_poll" datasource="#dsn#">
SELECT TOP 1 portal_polls.poll_id, portal_polls.question, portal_polls.answer1,
portal_polls.answer2, portal_polls.answer2, portal_polls.answer4,
portal_polls.answer4, portal_polls.number_answers
FROM portal_polls
WHERE (portal_polls.start_date < #Now()# and portal_polls.end_date > #Now()#)
and (everyone=1
OR exists (SELECT DISTINCT poll_id FROM portal_groups_x_polls,
portal_groups_x_students WHERE
portal_groups_x_polls.poll_id=portal_polls.poll_id and
portal_groups_x_students.group_id=portal_groups_x_polls.group_id and
portal_groups_x_students.student_user_id=#session.userid#))
and not exists (SELECT poll_id FROM portal_students_x_polls_completed
WHERE portal_students_x_polls_completed.poll_id=portal_polls.poll_id and
portal_students_x_polls_completed.student_user_id=#session.userid#)
ORDER BY NEWID();
</cfquery>
Thanks Everyone!
Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com
Great advertising can't be either/or. It must be &.
Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugged
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346515
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
