| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 03 November 2003 : 14:08:19
|
I have a table called "member_medals" with 3 fields, one of them being the MEMBER_ID.
I want write a select statement and loop to display the member name with the other 3 fields data.
The M_NAME is only in the FORUM_MEMBERS table though.
If I write a select statement for the MEMBER_MEDALS table, but would also like to pull the M_NAME that goes with the MEMBER_ID - how do I cross reference the two tables?
quote:
SELECT MEMBER_ID, 'm_name from forum_members?', MEDAL_ID FROM MEMBER_MEDALS ...
|
Alfred
The Battle Group
CREDO
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 03 November 2003 : 14:28:25
|
It would be something similar to this:
SELECT MEMBER_MEDALS.MEMBER_ID, FORUM_MEMBERS.M_NAME
FROM FORUM_MEMBERS INNER JOIN MEMBER_MEDALS ON FORUM_MEMBERS.MEMBER_ID = MEMBER_MEDALS.MEMBER_ID;
I am not sure of your respective field names though. I am assuming you have a MEMBER_ID field in the MEMBER_MEDALS table.
|
 |
Edited by - dayve on 03 November 2003 14:29:57 |
 |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 14:52:04
|
The field names are correct.
Would it not have to look like this:quote:
strSql = "SELECT MEMBER_MEDALS.MEMBER_ID, FORUM_MEMBERS.M_NAME FROM FORUM_MEMBERS INNER JOIN MEMBER_MEDALS ON FORUM_MEMBERS.MEMBER_ID = MEMBER_MEDALS.MEMBER_ID;"
set myMEDALMembers = my_Conn.Execute(strSql)
do while not myMedalMembers.eof
response.write myMedalMembers("MEMBER_ID") & ": " & myMedalMEMBERS("M_NAME") & myMedalMEMBERS("Medal_NAME") & "<BR>"
myMembers.movenext
loop
but it cannot find something: quote:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/BG/forums/Admin_medals_Add_handler.asp, line 36
linr 36 is the display line (red) |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 16:24:35
|
| You didn't have MEDAL_NAME in the SELECT. Does the medal_name exist in the member_medals table ? |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 03 November 2003 : 16:31:39
|
strSql = "SELECT MEMBER_MEDALS.MEMBER_ID, MEMBER_MEDALS.MEDAL_NAME, FORUM_MEMBERS.M_NAME FROM FORUM_MEMBERS INNER JOIN MEMBER_MEDALS ON FORUM_MEMBERS.MEMBER_ID = MEMBER_MEDALS.MEMBER_ID;"
set myMEDALMembers = my_Conn.Execute(strSql)
do while not myMedalMembers.eof
response.write myMedalMembers("MEMBER_ID") & ": " & myMedalMEMBERS("M_NAME") & myMedalMEMBERS("Medal_NAME") & "<BR>"
myMembers.movenext
loop |
The UK MkIVs Forum |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 16:50:19
|
| David, you might be right, but I wasn't sure on MEMBER_MEDALS.MEDAL_NAME |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 17:11:21
|
The field is actually called Medal_ID, and when I substituted that one thing it did display correctly.
Thanks for pointing me in the right direction - got to go and do some more work on this now! |
Alfred
The Battle Group
CREDO
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 18:49:57
|
I get this error for the red line below : quote:
Error Type:
Microsoft VBScript runtime (0x800A01F5)
Illegal assignment: 'DisplayMedalIcon'
/BG/forums/decorated.asp, line 94
quote:
currid = myMedalMembers("member_id")
do while not myMedalMembers.eof
If currid <> mymedalMembers("member_id") then
response.write "<body bgcolor=""#6600FF""><br><div align=""center"">"
response.write "<table bgcolor=""#37008A"" border=""5"" cellpadding=""5"" cellspacing=""0"" style=""border-collapse: collapse"" bordercolorlight=""#FFFFCC"" bordercolordark=""#666600"" bgcolor=""#000000"">"
response.write "<tr><td width=""100%"" align=""center""><p align=""center""><font color=""#FFFFCC""><BR>"
response.write "<a href=""http://www.ggholiday.com/bg/forums/pop_profile.asp?mode=display&id=6"">" & myMedalMembers("M_NAME") & "</a></font>"
response.write "<font size=""-1""> / <i>" & myMEDALMembers("M_COUNTRY") & "<br></p>"
response.write DisplayMedalIcon(myMedalMembers("member_id"))
response.write "</p></td></tr></table></center></div><br>"
DisplayMedalIcon = ""
currid = mymedalMembers("member_id")
End If
myMedalMembers.movenext
if not myMedalMembers.EOF then
End If
loop
What is illegal about it? |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 18:56:14
|
| DisplayMedalIcon seems to be a function, so you can't set it to a value. |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 19:10:15
|
I have taken bits of wisdom from past struggles, where you gave me this: quote:
Posted - 04 August 2003 : 18:35:38
--------------------------------------------------------------------------------
You can do it, you just need to keep track of when the member name changes.
Try this :
- just above your While line add :
currid = mymedals("member_id")
- Remove the Response.Write (but we'll need it shortly)
- After the movenext line, add this :
If currid <> mymedals("member_id") then
*add Response.Write line here*
DisplayMedalIcon = ""
currid = mymedals("member_id")
End If
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=46461&whichpage=2&SearchTerms=INNER%20JOIN |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 19:35:56
|
Alfred, if you look at this code that you posted before :
response.write DisplayMedalIcon(myMedalMembers("member_id"))
response.write "</p></td></tr></table></center></div><br>"
DisplayMedalIcon = ""
The first line indicates that DisplayMedalIcon is a function.
On the post you have referenced from the past, it was indeed a variable and you could set it to "", but not in the instance you are using at the moment. |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 20:27:55
|
| Yep, that's fine, but you still can't do what you are doing on line 94. If anything you should move line 94 to line 9. That might fix a problem that you are eluding to. |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 21:09:28
|
Ok, I did that, but still don't understand why this line is illegal now while it was in the same place in the old file.
It does display two of the three names with medals now, but one is missing. Why would that be? |
Alfred
The Battle Group
CREDO
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 November 2003 : 23:02:53
|
| Does that missing member have any medals ? |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 03 November 2003 : 23:15:53
|
Yes, there should be three with medals. Two have 4, and one has 2.
One with 4 is missing (Avenger).
I suspect that the first record in the select statement gets passed up because of this part: quote:
currid = myMedalMembers("member_id")
do while not myMedalMembers.eof
If currid <> mymedalMembers("member_id") then
|
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 03 November 2003 23:39:04 |
|
|
|
Topic |
|
Topic Locked
