| Author |
 Topic  |
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
 Posted - 12 September 2007 : 20:14:47
|
The following code works :-
'## Forum_SQL - Find all records with the search criteria in them
strmembernm = Request.QueryString("mem")
strSql = "SELECT " &strTablePrefix & "PM.M_FROM, " &strTablePrefix & "PM.M_ID, " &strTablePrefix & "PM.M_SUBJECT, "&strTablePrefix & "PM.M_TO, " &strTablePrefix & "PM.M_SENT "
strSql = strSql & " FROM " &strTablePrefix & "PM "
strSql = strSql & " ORDER BY " & strTablePrefix & "PM.M_FROM ASC"
but i like the PM_FROM and the PM_TO to give the members name from the members table instead of the members i.d, can you please help. |
Phil |
|
|
gary b
Junior Member
USA
267 Posts |
Posted - 12 September 2007 : 23:04:39
|
quote:
Originally posted by philsbbs
The following code works :-
'## Forum_SQL - Find all records with the search criteria in them
strmembernm = Request.QueryString("mem")
strSql = "SELECT " &strTablePrefix & "PM.M_FROM, " &strTablePrefix & "PM.M_ID, " &strTablePrefix & "PM.M_SUBJECT, "&strTablePrefix & "PM.M_TO, " &strTablePrefix & "PM.M_SENT "
strSql = strSql & " FROM " &strTablePrefix & "PM "
strSql = strSql & " ORDER BY " & strTablePrefix & "PM.M_FROM ASC"
but i like the PM_FROM and the PM_TO to give the members name from the members table instead of the members i.d, can you please help.
Have you tried this:
strmembernm = Request.QueryString("mem")
strSql = "SELECT " &strTablePrefix & "PM.M_FROM, " &strTablePrefix & "PM.M_FirstName, " &strTablePrefix & "PM.M_LastName, " &strTablePrefix & "PM.M_SUBJECT, "&strTablePrefix & "PM.M_TO, " &strTablePrefix & "PM.M_SENT "
strSql = strSql & " FROM " &strTablePrefix & "PM "
strSql = strSql & " ORDER BY " & strTablePrefix & "PM.M_FROM ASC"
I do not know the field names. Replace FirstName and LastName with correct field names.
Assuming the amended query works, is the page configured to display the new fields (data) returned by the query?
|
 |
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 14 September 2007 : 15:14:29
|
| This relates to private messaging and there is no firstname and lastname fields on ly the FROM and TO fields which are the same as the members i.d in the members table. |
Phil |
|
|
|
balexandre
Junior Member
Denmark
418 Posts |
Posted - 15 September 2007 : 13:32:47
|
so, just use 2 INNER JOIN (if you are using Access or MS SQL)
I don't know the tables or names, but here I give you what you need to have:
SELECT
UserFrom.FirstName + ' ' + UserFrom.LastName,
UserTo.FirstName + ' ' + UserTo.LastName,
PM.M_Subject, PM.M_Sent
FROM
PM
INNER JOIN UserTable UserFrom ON PM.M_FROM = UserFrom.UserID
INNER JOIN UserTable UserTo ON PM.M_TO = UserTo.UserID
ORDER BY
UserFrom.FirstName + ' ' + UserFrom.LastName ASC;
so, you are selecting your PM table and 2 times the user table, one for the FROM and one for the TO
the inner join will act as a glue between the UserID in the User Table and the ID in the PM table
easy right?  |
Bruno Alexandre
(Strøby, DANMARK)
"a Portuguese in Danmark"
|
Edited by - balexandre on 15 September 2007 13:33:40 |
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 15 September 2007 : 15:51:10
|
from the pm table
=
strSql = "SELECT " &strTablePrefix & "PM.M_FROM, " &strTablePrefix & "PM.M_ID, " &strTablePrefix & "PM.M_SUBJECT, "&strTablePrefix & "PM.M_TO, " &strTablePrefix &
=
i need M_FROM to join to M_USERNAME in the members table
and also M_TO to join to M_USERNAME in the members table.
hope this makes sense. |
Phil |
|
|
|
philsbbs
Junior Member
United Kingdom
397 Posts |
Posted - 16 September 2007 : 09:00:17
|
| i've done this if anyone else has private messages mod installed and wants a copy, feel free to email me. |
Phil |
|
|
| |
Topic |
|
|
|
Topic Locked
