[RESOLVED] Problem with displaying duplicate results

NiallThistle

Think I've got myself caught in a circle here, can't seem to figure out why my query is displaying eight copies of each result?

Any ideas?

			
$get_prevvals = mysql_query("
SELECT V.valuation_request as valreq,
V.valuation_total as total				
FROM val_valuations V, val_contracts T
WHERE V.valuation_job = $con
AND V.valuation_total != '0'
ORDER by V.valuation_request ASC
",$objConnect);

while($data = mysql_fetch_array($get_prevvals))
{

echo"
<tr>
<td align=\"right\" width=\"85%\" height=\"35\">Amount requested for Payment No. $data[valreq]</td>
<td align=\"center\" width=\"5%\">£</td>
<td align=\"right\" width=\"$fc%\">$data[total]<br></td>
</tr>";

bradgrafelman

Sounds like you have 8 rows in the val_contracts table.

Your query is telling MySQL to do a Cartesian product of every row in val_contracts with every row in val_contracts. Since you never specify any condition in the WHERE clause to eliminate some of those rows generated due to the val_contracts table, MySQL returns all of them in the product.

NiallThistle

That does actually correlate. There were eight records in the table.

But it is only displaying four of the results, the four I actually want it to - but it's displaying them all eight times.

Which part of my code is causing it to count the total records and display each record that many times?

NiallThistle

Ah, I see now. I had original used V.valuation_job = T.contract_id instead of V.valuation_job = $con. Keeping the val_contracts T in FROM was causing it.

Thank you!

bradgrafelman

NiallThistle;10988131 wrote:
That does actually correlate. There were eight records in the table.

But it is only displaying four of the results, the four I actually want it to - but it's displaying them all eight times.

Right, that's exactly what I would expect based on that query. Your WHERE condition eliminates some of the rows from the val_valuations table, but the remaining (four, apparently) rows are then used in a Cartesian product with every row in val_contracts.

NiallThistle;10988131 wrote:
Which part of my code is causing it to count the total records and display each record that many times?

It's not a part of your code, it's the SQL query itself:

FROM val_valuations V, val_contracts T

That tells MySQL this: "Take every row in V and join it with each row in T." In other words, you're doing a Cartesian product, and you'll have |V| * |T| rows in the result set (where |V| and |T| are the number of rows in each table; so if V has 4 rows and T has 8 rows, your result set will contain 32 rows).

EDIT: Replied before I saw your most recent post.

That new WHERE condition will indeed eliminate those superfluous rows generated from the Cartesian product with the T table. Hopefully the reason for the difference in result sets is apparent now.