How to break up nested Query's

[jimmyp3016]

Hey Guys,

I am running an older verison of sql on my webhost and it does not support nested querys which was enabled in 4.1 i think.

How can I break up this statement so my code will run on my server?

[code] $sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN (";
$sql .= "  SELECT DISTINCT u.referral FROM user u, transaction t WHERE u.id=t.userID";
$sql .= ") GROUP BY id";[/code]

Any help would be great thanks

[Destruction]

$query1 = mysql_query("SELECT DISTINCT u.referral FROM user u, transaction t WHERE u.id=t.userID");
$idlist = array();
while($row = mysql_fetch_assoc($query1)) {
    $idlist[] = $row['referral'];
}
$idlist = explode(",", $idlist);
$query2 = mysql_query("SELECT *, COUNT(id) AS referrals FROM user WHERE id IN ($idlist) GROUP BY id");

Or something similar...

HTH

Dest