Order by different table :S

[Owenmelbz]

Hi, Searched for ages and bit confused now lol so basically this is the situation. For my gaming website i got a roster/members list echoing out however i want to order it by rank so normally you'd use

 

$roster = mysql_query("SELECT * FROM roster WHERE gameplayed='Call of Duty 4' ORDER by rank");

 

however the roster table is like this

 

id            int

name          varchar

forumid              int

 

and well the player_profile page is well loads. but rank is varchar anyway and forumid is the primary field in player_profile

 

so basically yes, I need it to order it by rank, however the content of rank is stored within the player_profile table and not the roster.

 

is it possible for me to do this?

 

I saw something about forgien keys so dno if im misreading this which i think i am, but i thought

 

if i added a rank field into the roster table which was linked to hold the same content as rank in the player_profile field it would work? but i dno how to go about doing this i got phpmyadmin and navicat on the go but not really sure what I'm doing apart from that

 

THANKS

 

- Owen

 

 

[rhodesa]

you need to read up on joining tables. but here is one that might work:

SELECT * FROM roster r LEFT JOIN player_profile p ON r.forumid = p.forumid WHERE r.gameplayed='Call of Duty 4' ORDER by p.rank

[Owenmelbz]

Ah yes i discovered a useful feature in navicat that helps u build queries lol, and it seems that it came with the same answer as u did lol.

 

SELECT

*

FROM

roster

Inner Join player_profiles ON roster.id = player_profiles.rosterid

WHERE

roster.gameplayed =  'Call of Duty 4 Beta'

ORDER BY

player_profiles.rank ASC"

 

thats what it came up as, so basically didnt use the alias and used join inner rather than left.

 

what would the difference between Left and Inner achieve out of interest?

 

thanks for you help tho didnt expect that quick reply lol

[corbin]

A LEFT JOIN will pull rows despite whether information exists in the right table or not.  In other words, it will fill columns with NULL for rows that can't be found in the other table.

 

 

An INNER JOIN only pulls rows that have data in both tables.

 

 

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[Owenmelbz]

oh right :S haha far to confusing for me but thanks for ur input and help much appreciated