query inside a loop

I read from other forum that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop.

$sql = "SELECT * FROM mytable order by points desc limit 10";
$rec = mysql_query($sql) or die(mysql_error());
$datas = mysql_fetch_array($rec);

do{
$sq = "Select * from secondtable where linkid = '$datas[id]'";
$rst  = mysql_query($sq) or die(mysql_error());
$rows = mysql_fetch_array($rst);
echo "$rows[somefield]";
}while($datas= mysql_fetch_array($rec));

Code (markup):

This works perfectly but I want the second query to be out of the loop if there is a way and how.

 

Do a simple join in the first query will stop the need for the second query.

Something like:

select * from mytable,secondtable WHERE secondtable.linkid = mytable.id

Note the array in your while loop will then contain all the returned values from both tables.

 

Thanks. that's cool, but how about limitting the first table to 10 and the second table to 1 how can I do that?

 

Id grab all the values in the while loop then seperate whichever values you want and limit the number of results there using php.

 

Something like this would be better I think.....

SELECT TOP 10 *

FROM
(SELECT TOP 10 * FROM
mytable
ORDER BY points) as Table1
INNER JOIN secondtable ON Table1.ID = secondtable.Id

What this does is get the top 10 records from the 1st table and joins them to the second.
because I assume this is a one to many you would get more than 10 records so you need to use TOP 10 again in the top query which should give you the first 10 from the first table and the first record from the seond table but you will need to play around with your ordering to get the records you want