Forum Moderators: open
Table 1:
NAME AVATAR
------ --------
Ron egg
Ken slime
Bert hi
Table 2:
NAME TROPHY
------ --------
Ron 44
Ron 45
Ron 55
Ken 22
Ken 09
Ken 03
Ken 88
Bert 11
Bert 12
Table 3:
NAME POINTS GAMES
------ ------- -------
Ron 765 999
Ken 998 222
Bert 222 333
Table 4:
NAME MEDAL
----- -------
Ron 96
Ron 13
Ken 35
Ken 90
Ken 32
Bert 10
Bert 76
Bert 49
Bert 57
Now you have to join these four tables in such a way that you count the trophies and medals of each user into a single unit 'achievement' and then sort them from high to low, so that the output should look like that:
NAME AVATAR ACHIEVEMENTS POINTS GAMES
------ -------- -------------- -------- ------
Ken slime 7 998 222
Bert hi 6 222 333
Ron egg 5 765 999
can you help me?
Now you have to join these four tables in such a way ...
We can't do homework here.
However I'll offer up this:
To join tables, you need one join field common to all the tables you want to join, or at least common to the "main table." You can have one table join on one field, a second table join on a completely different field, but the relational tables must join on one of the fields in the other tables in the query.
Normally, for speed and efficiency, this is a numeric field. In your example, it appears that join field is textual. So whoever gave you this test is giving you a very bad example for table joins (and I will refrain from further comment . . . )
For that reason only, I'll give you a hint.
members
record_id¦user_id¦fname¦lname
posts
record_id¦post_id¦user_id¦title¦message
Create links to posts that display the member name and titles of their posts for user 1234:
select members.fname,members.lname,posts.post_id,posts.title from members,posts where members.user_id=posts.user_id and members.user_id=1234;
.... as results are returned, we store the post id field in $post_id, title in $post_title, ... etc.
<a href="some-script.php?p=$post_id">$post_title by $fname $lname</a>
Note how the second table is joined on the first by one particular field, and the "from" includes all tables in the query. Multiply that by 4 and you will get it.
