This is a multi-part message in MIME format.
------=_NextPart_000_000F_01BFA45C.9AE1D0A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi there!
I'm having a very weird problem...
I'm building a system where users can subscribe to some courses over the =
net, my database looks like this:
Students=20
-------------
ID | Name | email | Phone
Courses
------------
IDcourse | NameCourse
SCourses (a table where I put the Student ID and tell which course is =
hi/her doing)
---------------
IDStudent | IDCourse
My template looks like this
<cfquery name=3DgetdistinctID datasource=3D"#application.dsn#">
SELECT DISTINCT IDStudent=20
FROM SCourses
WHERE IDCourse IN (#ID#)
</cfquery>
Ok, I pass an Course ID to the template and it returns to me the name of =
the students on it
then I create a list
<cfset list=3D"">
<cfoutput query=3DgetdistinctID>
<cfset list =3D ListAppend(list, IDStudent)>
</cfoutput>
<cfquery name=3D"getstudents" datasource=3D"#application.dsn#">
SELECT Students.ID, Students.Name, Students.email,
Courses.IDCourse, Courses.CourseName,
SCourses.IDStudent, Scourses.IDcourse
FROM Students, Courses, Scourses
WHERE Students.ID IN (#list#)
AND Scourses.IDStudent =3D Students.ID
AND Scourses.IDCourse =3D Courses.IDCourse =20
</cfquery>
When Generating a report it will looks like this
ID Name Email Courses
10 MIKE [EMAIL PROTECTED] AutoCad 2000 Basic
21 Bruce [EMAIL PROTECTED] MapGuide Basic/3D Studio Basic
And it goes. But if my list of ID gets bigger than 8 elements for my=20
surprise looks what happens:
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
13 Kate [EMAIL PROTECTED] AutoLisp
I tried to show my list just for debugging purpposes, and its ok
the problem is in the query I belive, my list for the display=20
above looks like this
list =3D 1,3,13,14,15,16,21,22,45,46
but only the user ID # 13 is shown.
Any idea guys?
Please I'll be in serious problems if I don't solve this.
Thanks a LOT
Vinicius Carvalho
Digicadd Brazil
Official AutoDesk Reseller
------=_NextPart_000_000F_01BFA45C.9AE1D0A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META content=3Dtext/html;charset=3Diso-8859-1 =
http-equiv=3DContent-Type>
<META content=3D'"MSHTML 4.72.3110.7"' name=3DGENERATOR>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#000000 size=3D2>Hi there!<BR>I'm having a very weird =
problem...<BR>I'm building a system where users can subscribe to some =
courses=20
over the net, my database looks like this:</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Students <BR>-------------<BR>ID | =
Name | email=20
| Phone</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Courses<BR>------------<BR>IDcourse =
|=20
NameCourse</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>SCourses (a table where I put the =
Student ID and=20
tell which course is hi/her doing)<BR>---------------<BR>IDStudent |=20
IDCourse</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>My template looks like =
this</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2><cfquery name=3DgetdistinctID=20
datasource=3D"#application.dsn#"><BR>SELECT DISTINCT =
IDStudent=20
<BR>FROM SCourses<BR>WHERE IDCourse IN =
(#ID#)<BR></cfquery></FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Ok, I pass an Course ID to the =
template and it=20
returns to me the name of the students on it</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>then I create a list<BR><cfset=20
list=3D""><BR><cfoutput =
query=3DgetdistinctID><BR> <cfset=20
list =3D ListAppend(list, =
IDStudent)><BR></cfoutput></FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2><cfquery =
name=3D"getstudents"=20
datasource=3D"#application.dsn#"><BR> SELECT =
Students.ID,=20
Students.Name, Students.email,<BR> Courses.IDCourse,=20
Courses.CourseName,<BR> SCourses.IDStudent,=20
Scourses.IDcourse<BR> FROM Students, Courses,=20
Scourses<BR> WHERE Students.ID IN=20
(#list#)<BR> AND Scourses.IDStudent =3D=20
Students.ID<BR> AND Scourses.IDCourse =3D=20
Courses.IDCourse <BR></cfquery></FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>When Generating a report it will =
looks like=20
this<BR>ID Name =20
Email &n=
bsp; =20
Courses</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>10 MIKE <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> &nbs=
p; =20
AutoCad 2000 Basic<BR>21 Bruce <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
MapGuide=20
Basic/3D Studio Basic</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>And it goes. But if my list of ID =
gets bigger=20
than 8 elements for my <BR>surprise looks what happens:</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp<BR>13 Kate <A=20
href=3D"mailto:[EMAIL PROTECTED]">[EMAIL PROTECTED]</A> =
=20
AutoLisp</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>I tried to show my list just for =
debugging=20
purpposes, and its ok<BR>the problem is in the query I belive, my list =
for the=20
display <BR>above looks like this</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>list =3D =
1,3,13,14,15,16,21,22,45,46</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>but only the user ID # 13 is =
shown.</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Any idea guys?<BR>Please I'll be in =
serious=20
problems if I don't solve this.<BR>Thanks a LOT</FONT></DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV>
<DIV><FONT color=3D#000000 size=3D2>Vinicius Carvalho<BR>Digicadd =
Brazil<BR>Official=20
AutoDesk Reseller<BR></FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT color=3D#000000 size=3D2></FONT> </DIV></BODY></HTML>
------=_NextPart_000_000F_01BFA45C.9AE1D0A0--
------------------------------------------------------------------------------
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.
