Left Join help!!

[benrob82]

Hi can anyone help me with this query

$getcampaign = mysql_query("select * from prospect_list_campaigns"); 
while ($gotcampaign = mysql_fetch_row($getcampaign)) 
{
	$getprospect = mysql_query("select id,contact_id from prospect_lists_prospects where prospect_list_id = '$gotcampaign[2]'"); 
	while ($gotprospect = mysql_fetch_row($getprospect)) 
	{
		$getcontacts = mysql_query("select id,first_name,last_name from contacts where contacts.id = '$gotprospect[1]' AND deleted = 0 order by last_name desc"); 
		while ($gotcontacts = mysql_fetch_row($getcontacts)) 
		{		
			$getcocontacts = mysql_query("select id,account_id from accounts_contacts where contact_id = '$gotprospect[1]'"); 
			while ($gotcocontacts = mysql_fetch_row($getcocontacts)) 
			{			
				$getcompany = mysql_query("select id,name from accounts where accounts.id = '$gotcocontacts[1]' AND deleted = 0 group by accounts.name order by accounts.name asc"); 
				while ($gotcompany = mysql_fetch_row($getcompany)) 
				{
					$count++;
					echo "$gotcompany[1]<br>";
				}		
			}
		}
	}
}

I need to get this as a join sql query but I cant get my head around it.

Please.

 

[r937]

i might be able to help you if you could please remove all the php code

r937.com | rudy.ca

 

[Kozusnik]

It's quite a set of whiles...

Personally, I'd simplify first like this...

SELECT * from prospect_list_campaigns t1, prospect_lists_prospects t2, contacts t3, accounts_contacts t4, accounts t5 WHERE t1.id = t2.id AND t2.idfield = t3.idfield (etc.)

I can't get the field names for the joins from the above code, but I think you can see where I'm going.

Once this code works, either use it, or create join statements for each table.

Mark