How to INNER JOIN three tables?

[naeemnur]

Hi,

All i want to show is the 'fr_players' to have two categories of 'fr_nations' and 'fr_clubs' and fetch the names from the foreign id...

Here what i have done so far but it doesnt shows any error or return any tables either:

SELECT a.id as club_id,
       a.fr_name as club_name, 
       b.id as nation_id,
       b.fr_name as nation_name,
       c.id as player_id,
       c.fr_fname as player_name"
    ." FROM fr_clubs as a 
    INNER JOIN fr_nations as b ON a.id = b.club_id
    INNER JOIN fr_players as c ON b.id = c.nation_id"
    ." LIMIT $paginate->start, $paginate->limit

here is the table structure:

CREATE TABLE IF NOT EXISTS `fr_clubs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,

`fr_name` varchar(100) NOT NULL,

  `fr_nickname` varchar(100) NOT NULL,

  `fr_chairman` varchar(100) NOT NULL,

  `fr_coach` varchar(100) NOT NULL,

  `league_id` int(11) NOT NULL COMMENT 'Foreign Key',

  `fr_logo` varchar(100) NOT NULL,

  `fr_bio` text NOT NULL,

  `fr_founded` varchar(50) NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='clubs Details' AUTO_INCREMENT=22 ;

    CREATE TABLE IF NOT EXISTS `fr_nations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `fr_name` varchar(100) NOT NULL,

  `fr_full_name` varchar(100) NOT NULL,

  `fr_nickname` varchar(100) NOT NULL,

  `fr_coach` varchar(100) NOT NULL,

  `fr_captain` varchar(100) NOT NULL,

  `fr_logo` varchar(100) NOT NULL,

  `fr_bio` text NOT NULL,

  `confed_id` int(11) NOT NULL COMMENT 'Foreign Key',

  `club_id` int(11) NOT NULL COMMENT 'Foreign Key',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='nations Details' AUTO_INCREMENT=37 ;

    CREATE TABLE IF NOT EXISTS `fr_players` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `nation_id` int(11) NOT NULL COMMENT 'Foreign Key',

  `fr_fname` varchar(100) NOT NULL,

  `fr_lname` varchar(100) NOT NULL,

  `fr_nickname` varchar(100) NOT NULL,

  `fr_position` varchar(100) NOT NULL,

  `fr_town` varchar(100) NOT NULL,

  `club_id` int(11) NOT NULL COMMENT 'Foreign Key',

  `fr_dob` varchar(100) NOT NULL,

  `fr_nation_number` float NOT NULL,

  `fr_club_number` float NOT NULL,

  `fr_image` varchar(50) NOT NULL,

  `fr_height` float NOT NULL,

  `fr_bio` text NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='players Details' AUTO_INCREMENT=23 ;

Any help will be appreciated, Thanks!

[urtrivedi]

$query="SELECT a.id as club_id,
       a.fr_name as club_name, 
       b.id as nation_id,
       b.fr_name as nation_name,
       c.id as player_id,
       c.fr_fname as player_name
     FROM fr_clubs as a 
    INNER JOIN fr_nations as b ON a.id = b.club_id
    INNER JOIN fr_players as c ON b.id = c.nation_id
     LIMIT ". $paginate->start .",". $paginate->limit

[naeemnur]

@utrivedi

thanks for the reply, but it is not working either...

MySQL returned an empty result set (i.e. zero rows)!

[smantscheff]

Show some test data (in form of INSERT statements) which should give a non-empty result.

[naeemnur]

Show some test data (in form of INSERT statements) which should give a non-empty result.

there is already data in the table...

[smantscheff]

there is already data in the table...

If you want help, show it. Or submit a test case which shows the undesired behaviour.

[drjohn]

You have the field `club_id` int(11) NOT NULL COMMENT 'Foreign Key', in the nation table
This doesn't make sense.
It implies that each nation only has a single club in it.
It would make more sense to me if the club table had it's nation id in there as a foreign key - which then suggests that a nation has many clubs in it.

So check your normalisation again.

Why have you got all those fr_ in front of almost every field? They serve no purpose other than to give you more typing to do and increase the risk of making a typo.

Also I'm willing to bet that no two nations have the same name - there is only one Scotland, one Germany, one Denmark. So you don't need a numerical id for the nations table acting as the primary key, the name of the nation itself is the correct, natural primary key. Using the name as the primary key will reduce the number of joins you will regularly require, as the numerical id always forces a join to find the nation's name. But using the name as the PK means you already have that info in the player table.

It's a common beginners mistake to assume that a numerical id field is always required, when there is already a unique natural primary key present which will simplify things.

Example

color table
1, red
2, green
3, lightgreen
4, dark red
5, purple
6, morrocan brown
7, sunset red
8, white
9, cream
10, Navy blue

now a car table
model, color, engine_id, etc
Cavelier, white, 742, etc
no join required to know the color

But Cavelier, 4, 345, etc DOES require a join to find out what color it is. You had to look at the list of colors, didn't you.

[drjohn]

What do you get if you run the query directly on your tables of data?

You are obviously using php, so to eliminate the possibility of errors in the php, start your testing at the database level, using just the sql.
If it doesn't work there, it will never work in your program / web page, will it?
If it does work there, the error is in your PHP

[naeemnur]

@drjohn Thanks for the detailed reply, this is my first project i'm dealing with tables, i'm more of a front-end developer...

here is the code, without the on-clause, all i get is same country and club for each player:

<?php   
$playersql = "SELECT a.id as club_id,
       a.fr_name as club_name, 
       b.id as nation_id,
       b.fr_name as nation_name,
       c.id as player_id,
       c.fr_fname as player_fname,
       c.fr_image as player_image"
       ." FROM fr_clubs as a INNER JOIN fr_nations as b INNER JOIN fr_players as c"
       ." LIMIT $paginate->start, $paginate->limit";
	
//echo $playersql;	 

$player_res = mysql_query($playersql);
$numrows = mysql_num_rows($player_res); //echo $numrows;
if($numrows == 0)
{
$error_msg= "&nbsp;No Players Found";
}
else
?>

<?php if(!$error_msg) { ?>
                    <table align="center" width="60%" >
                    <tr><td colspan="4" align="right"><a href="add-player.php">Add new Player</a>
                    </td></tr>
                    <tr>
			<td class="middlearea" valign="top">
			<table cellspacing="0" cellpadding="10" width="100%" height="100%">
				<tr>
                    <th width="22%" align="left">Image</th>
                    <th width="22%" align="left">Name</th>
                    <th width="29%">Country</th>
                    <th width="29%">Club</th>
                    <th width="33%">Actions</th>
                  </tr>                   
                <?php 
			   while($player_row = mysql_fetch_array($player_res))
				{
			    ?>    
                  <tr>     
               
                    <td valign="top"><?php echo "<img width=\"100\" src=\"../images/players/".$player_row['player_image']."\">"; ?></td>
                    <td valign="top"><?php echo $player_row['player_fname']; ?><?php echo "&nbsp;"?><?php echo $player_row['player_lname']; ?></td>
                     <td valign="top"><?php echo $player_row['nation_name']; ?></td>
                    <td valign="top"><?php echo $player_row['club_name']; ?></td>
                    <td valign="top"><a href="edit-player.php?nation_id=<?php echo $player_row['nation_id'];?>">Edit</a> | <a href="delete-player.php?player_id=<?php echo $player_row['player_id']; ?>" onClick="return confirm('This action will delete this?\n Are you sure to continue?');">Delete</a></td>
                  </tr>                 
                  
                  <?php } ?>       
                  <tr>
                    <td colspan="6" align="center"></td>
                    </tr>                        
		           </table>                    
                    </td>
			    </tr>
                    </table>

and here is the screenshot:
http://i.imgur.com/6UkGk.png

[drjohn]

This query works.

SELECT a.id as club_id,
a.fr_name as club_name,
b.id as nation_id,
b.fr_name as nation_name,
c.id as player_id,
c.fr_fname as player_name
FROM fr_clubs as a
INNER JOIN fr_nations as b ON a.id = b.club_id
INNER JOIN fr_players as c ON b.id = c.nation_id;

I made your database, entered some date, ran the query ON THE DATABASE, not using the php in any way at all. I entered via phpmyadmin, on my test server, and got back the correct number of rows with the correct data in place.

As I said...Always check your queries directly on the database without using your application, web site, php or anything else that stands between the query going in and the data coming out.

So now you know that somewhere in your php you have an error in creating your output.

Try echoing the number of rows returned by your query at the top of your page - you have already got that as a variable, $numrows.
Are there more rows in the dataset returned than are displayed? You should get one row per player in your players table.

What do you get if you dump this line in your code ." LIMIT $paginate->start, $paginate->limit ???

[naeemnur]

This query works.

SELECT a.id as club_id,
a.fr_name as club_name,
b.id as nation_id,
b.fr_name as nation_name,
c.id as player_id,
c.fr_fname as player_name
FROM fr_clubs as a
INNER JOIN fr_nations as b ON a.id = b.club_id
INNER JOIN fr_players as c ON b.id = c.nation_id;

Not working, checked in phpmyadmin with removing the last line:

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0009 sec )

[drjohn]

Then you don't have the correct set of relationships! You can't have the correct foreign keys between the players, club and nations. Because I made sure that the test data set I used DID have each club linked to a nation and each player linked to a club, and I got back exactly the correct rows that I expected.

So, write down a set of club and nation data and match the foreign keys so that a link is correctly established. Then add a couple of players with foreign keys that link them to an existing club and nation.
OR
You could print out each table's contents and scrutinise then to make sure there is any data to be returned. Change a few bits to match existing clubs and nations in your player team.

Although eventually you are going to have to fix the wrongly normalised tables (and then your queries) as at present you can only have one club per nation, as I said earlier. Working with badly designed tables just doesn't help.

[naeemnur]

Then you don't have the correct set of relationships! You can't have the correct foreign keys between the players, club and nations. Because I made sure that the test data set I used DID have each club linked to a nation and each player linked to a club, and I got back exactly the correct rows that I expected.

So, write down a set of club and nation data and match the foreign keys so that a link is correctly established. Then add a couple of players with foreign keys that link them to an existing club and nation.
OR
You could print out each table's contents and scrutinise then to make sure there is any data to be returned. Change a few bits to match existing clubs and nations in your player team.

Although eventually you are going to have to fix the wrongly normalised tables (and then your queries) as at present you can only have one club per nation, as I said earlier. Working with badly designed tables just doesn't help.

I followed your instructions and updated my database it finally worked!!! Thank you so much for helping me out on this one, really appreciate it :)

i did this query:

"SELECT 
	a.id as nation_id,
	a.fr_name as nation_name,
	b.id as player_id,
	b.fr_fname as player_fname,
	c.id as club_id,
	c.fr_name as club_name" 
	." FROM fr_nations a 
	INNER JOIN fr_players b ON a.id = b.nation_id
	INNER JOIN fr_clubs c ON c.id = b.club_id"
	." LIMIT $paginate->start, $paginate->limit" ;

And it returned everything correctly :D