JOIN - 3 tables

[Taylor_1978]

Howdy,

I've used JOIN many times before including 3 or more tables but at the moment I am left scratching my head.

These are my tables:

TEAMS:
tid team id number
teamname team's name

PLAYERS:
pin player id number
playername player's name

RELATIONS:
rid relation id
pin player id number
tid team id number
status Captain, Vice-Captain or Player


Okay so basically, all the teams are listed in table "teams" and all the players are listed in "players" - all pretty obvious so far.

Then in table "relations", it records what team a player is in and if they are Captain, Vice-Captain or a Player. The purpose of this relations table is because a player may be a member of two or even more teams.

Now, I've tried numerous different ways of join queries and haven't come up with the right one so far.

If I was to do a query eg. $select = mysql_query("SELECT.... JOIN.. etc);
then
while($row = mysql_fetch_array($select)) {

etc

}

$row[0] should be the team's name and every team should be listed
$row[1] would show the captain's name if the team has one, and if not - it will leave it blank.

But every combination I try doesn't seem to work and I either end up with the team listed multiple times OR teams that do not have captains/players yet don't get listed at all.

Any help much appreciated! :thumbsup:

Taylor.

[guelphdad]

first do all your coding in sql only, don't do any in php. when your code works then integrate it in php.

can you show some sample rows of data as well as your expected output?

it sounds like you have to separate issues here. the first is to get the data you want out and the second, separate from mysql, is displaying things the way you want. If you listed the Buffalo Sabres players for instance it sounds like you only want the team name listed once and then every player listed.

For your display issues see this article (http://guelphdad.wefixtech.co.uk/sqlhelp/catsubcat.shtml), but we'll need to see your sample rows and output as well as your query to help with the other part.