| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 29 July 2003 : 17:00:12
|
I have a table in my Access database called "member_medals" with these 3 text fields:
ID
MEMBER_ID
MEDAL_ID
If I would like to query that table now, inorder to display the member names on a page, how do I go about it?
All I know how to do is "select" the id numbers, as in:
select * from member_medals , etc..., but that will not give me the names!
|
Alfred
The Battle Group
CREDO
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
Nikkol
Forum Moderator
USA
6907 Posts |
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 29 July 2003 : 17:47:27
|
D3mon: the names are not in that table, only the member_IDs from FORUM_Members.
Nikkol: How do I do an "inner join"? |
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 29 July 2003 17:48:47 |
 |
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 29 July 2003 : 20:05:05
|
Alfred, you use Access db's right ? and this is a brand new page ? to display every user's name and their medals ?
The easiest way (if using Access) is to create a new Query, and play around with it until you get the output that you want. Then switch to SQL view, and cut & paste the SELECT into your ASP code. I'll have a look at the code tonight, but there's people here that can do inner joins with one hand  |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 29 July 2003 : 22:50:40
|
Well, I fooled around in the help files of Access, and they talk about inner joins there.
But, as usual, the guidance is obscure and confusing:
"In query design view (wherever that is!) drag the field from one table into the field of the other table".
I somehow managed to open the two tables next to each other, but nothing let me drag it between them.
|
Alfred
The Battle Group
CREDO
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 30 July 2003 : 00:04:03
|
strSql = "SELECT M.MEMBER_ID, M.MEDAL_ID, M1.M_NAME " & _
"FROM MEMBER_MEDALS M " & _
"INNERJOIN FORUM_MEMBERS M1 ON M1.MEMBER_ID = M.MEMBER_ID;" |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 30 July 2003 : 00:06:22
|
I wouldn't even do that - open Query Designer and add the two tables. Then just choose the fields you want to double-clicking on them to create the query you want. Then execute the query (the red ! button) and check the results. *IF* the results come out wierd then you need to check the links between the tables.
To get into Query Mode, open your database and there should be a menu on the left that goes Tables, Queries, Forms, Modules (or something along those lines). Click on Query then Design new Query |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 30 July 2003 : 01:01:11
|
Somehow I got the two tables combuned, then I went into SQL mode and saw this: quote:
SELECT FORUM_MEMBERS.M_NAME, MEMBER_MEDALS.ID, MEMBER_MEDALS.member_ID, MEMBER_MEDALS.medal_ID
FROM FORUM_MEMBERS INNER JOIN MEMBER_MEDALS ON FORUM_MEMBERS.MEMBER_ID = MEMBER_MEDALS.member_ID;
Got to build on that in the morning - thanks for now, both of you! |
Alfred
The Battle Group
CREDO
|
|
|
|
Anacrusis
Junior Member
USA
219 Posts |
Posted - 30 July 2003 : 07:51:45
|
quote:
Originally posted by Alfred
I have a table in my Access database called "member_medals" with these 3 text fields:
ID
MEMBER_ID
MEDAL_ID
Those are all text fields? Will he run into problems joining member_medals.MEMBER_ID (text) to FORUM_MEMBER.MEMBER_ID (int) ? |
The Internet ClubHouse
www.internet-clubhouse.com
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 30 July 2003 : 07:58:48
|
| How can you tell they are text fields ? |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 30 July 2003 : 11:22:10
|
quote:
Originally posted by GauravBhabu
strSql = "SELECT M.MEMBER_ID, M.MEDAL_ID, M1.M_NAME " & _
"FROM MEMBER_MEDALS M " & _
"INNERJOIN FORUM_MEMBERS M1 ON M1.MEMBER_ID = M.MEMBER_ID;"
I tried this in the new page, and when I link to it I get htis error: quote:
Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment: 'DisplayMedalIcon'
/BG/forums/decorated.asp, line 24
The text file for "decorated.asp" is here: http://www.ggholiday.com/bg/forums/decorated.txt
PS.: I have installed the page on a manual basis for now, so something can be seen in the meantime, and give you an idea of what I am trying to do.
But it does of course not get populated from the database yet - I have to keep an eye on the data and upload changes for now.
From here:
http://www.ggholiday.com/bg/forums/DEFAULT.ASP
the link "honored members" will bring up this page:http://www.ggholiday.com/bg/forums/decorated.ASP |
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 31 July 2003 13:02:14 |
|
|
|
Anacrusis
Junior Member
USA
219 Posts |
Posted - 31 July 2003 : 15:26:52
|
quote:
Originally posted by laser
How can you tell they are text fields ?
quote:
I have a table in my Access database called "member_medals" with these 3 text fields:
I just assumed... I'm probably wrong though. |
The Internet ClubHouse
www.internet-clubhouse.com
|
Edited by - Anacrusis on 31 July 2003 15:29:59 |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 31 July 2003 : 18:01:35
|
Oops, sorry Anacrusis - I forgot Alfred said text. Alfred, if they really are text I doubt that the inner join will work.
OK Alfred, looking at your code I see that the medals on decorated.asp are different to the medals on members.asp. How are you going to do this ? I guess by having a small & large version of each medal but I haven't seen that in the table yet 
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 31 July 2003 : 21:08:55
|
I use the same images of the medals for all pages, just sizing them as needed on the page.
So far you can only see a few on members.asp of all those that are in the member_medals table.
That should not present a problem, should it? |
Alfred
The Battle Group
CREDO
|
Edited by - Alfred on 31 July 2003 22:18:41 |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 01 August 2003 : 01:14:36
|
| I'll double-check later, but I was sure that the filenames on decorated.asp and members.asp are different - (aren't they ?) |
|
|
|
Topic |
|
Topic Locked
