Sum display error

[Borderline]

Good evening

I'm running a tipping competition, and am hoping to make the updating slightly less tedious. The code currently reads as follows:

<?php

    $query = " SELECT SUM(comptipsterselections.profit) as Profit, comptipsterselections.stable, comptipsterboard.link
       FROM comptipsterselections INNER JOIN comptipsterboard
       ON comptipsterselections.stable=comptipsterboard.stable
       WHERE comptipsterselections.comp = 'aintree 2010'
       GROUP BY comptipsterselections.stable, comptipsterboard.link
       ORDER BY SUM(comptipsterselections.profit) DESC";

    $result = mysql_query($query) or die(mysql_error());
    // Set-up table
       echo "<table class='correctenglish' border='1' cellpadding='4' cellspacing='0' width='75%'>";
       echo "<tr class='toprow'> <th>Stable</th> <th>Daily Profit</th></tr>";

    // Print out result
       while($row = mysql_fetch_array($result)){
       $link='/site/competitions/tipster'.$row[link];
       echo "<tr><td>";
       echo "<a href='$link'>";
       echo $row[stable];
       echo "</td><td>";
       echo " &#163;". $row[Profit];
       echo "</td></tr>";
       }
       echo "</table>";
       ?>

The profit for each player is showing correctly for each player. However, the printed results now show links for each competition the player has taken part in, not just the Aintree 2010 competition: Click Here I thought the WHERE clause would filter out anything that didn't have Aintree 2010 in the comp column, but this isn't the case. Is there any advice for getting this added to the code?

The two tables of data look like this:

comptipsterselections: http://www.further-flight.co.uk/site/competitions/tipster/aintr/sp/comptipsterselections.jpg
comptipsterboars: http://www.further-flight.co.uk/site/competitions/tipster/aintr/sp/comptipsterboard.jpg

Any advice gratefully accepted!

[adam_k]

I'm guessing that the problem is that you need to join your 2 tables with both stable and comp columns.

[|-|x]

Try changing from INNER JOIN to LEFT JOIN as your filter is on the left hand table.

[adam_k]

@Hearth: This would still cause a cartesian product. Plus left and right are used when you want to include records that don't have matching keys in the joined table. This is not that case. This is not limiting enough the records joined.

[Borderline]

Thank you Adam K - works perfectly now.