Taking a guess at how your tables are structured, but try this . . .
1. alias the table names in case there are fields with the same name - it
also makes it easier to read by others who don't know your tables
2. make sure to join the two tables using primary/foriegn key (parent/child)
relationships, otherwise you get a cartesian join (and, no, I don't really
know what that means either, you just get all the records from both tables)
3. here is a stab at your query using Oracle table aliasing (Access needs an
"as" I believe, not sure about SQL Server)
<CFQUERY NAME="retrievemessages" DATASOURCE="#dsn#">
SELECT m.MessageID, m.MessageSenderUserID, m.MessageDate,
m.MessageSubject, m.MessageMessage, m.MessageRead,
m.MessageRepliedTo, m.MessageUrgent, u.UserName
FROM Message m, UserInfo u
WHERE m.MessageRecipientUserID = '#session.UserID#'
AND m.MessageSenderUserID = u.UserID
and u.UserID = '#MessageSenderUserID#'
</CFQUERY>
HTH,
Dan
-----Original Message-----
From: Kevin Schmidt [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 02, 2000 8:07 AM
To: CF-Talk
Subject: SQL Question
This is a multi-part message in MIME format.
------=_NextPart_000_000A_01C044B4.AD1069F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Gotta question. This is my lack of advanced SQL knowledge making itself =
known.
Here is the query:
<CFQUERY NAME=3D"retrievemessages" DATASOURCE=3D"#dsn#">
SELECT MessageID, MessageSenderUserID, MessageDate, MessageSubject, =
MessageMessage, MessageRead, MessageRepliedTo, MessageUrgent, UserName
FROM Message, UserInfo
WHERE MessageRecipientUserID =3D '#session.UserID#' AND
I need the value below to be the current value of the query. Basicalyl =
I have selected the messagesenderuserid and I need to pull that persons =
name from the UserInfo table by that ID.
UserID =3D '#MessageSenderUserID#'
</CFQUERY>
Thanks for the help!
Kevin
------=_NextPart_000_000A_01C044B4.AD1069F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Gotta question. This is my lack =
of advanced=20
SQL knowledge making itself known.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Here is the query:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><CFQUERY NAME=3D"retrievemessages"=20
DATASOURCE=3D"#dsn#"><BR>SELECT MessageID, MessageSenderUserID, =
MessageDate,=20
MessageSubject, MessageMessage, MessageRead, MessageRepliedTo, =
MessageUrgent,=20
UserName<BR>FROM Message, UserInfo<BR>WHERE MessageRecipientUserID =3D=20
'#session.UserID#' AND<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I need the value below to be the =
current value of=20
the query. Basicalyl I have selected the messagesenderuserid and I =
need to=20
pull that persons name from the UserInfo table by that ID.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>UserID =3D=20
'#MessageSenderUserID#'<BR></CFQUERY></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for the help!</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Kevin</FONT></DIV></BODY></HTML>
------=_NextPart_000_000A_01C044B4.AD1069F0--
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
The information contained in this message is privileged and confidential. It is
intended solely for the use of the named recipient. If you are not the intended
recipient, you are hereby notified that any disclosure, copying, distribution, or use
of the contents of this transmission is strictly prohibited. If you receive this
message in error, please notify the sender immediately. Thank you.
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
