MySQL Join

[spertuit]

Can anyone give me a hand turning this into a join statement?

$query1 = sprintf("SELECT * FROM companies WHERE status = 1");
$result1 = mysql_query($query1);
while ($row1 = mysql_fetch_assoc($result1)) {
	$C1=$row1["companyName"];
	$C3=$row1["id"];
	$C4=$row1["businessType"];
		
$query="SELECT * FROM companyAdmins WHERE companyID = '$C3'";
$result=mysql_query($query);
$num=mysql_numrows($result);
$email=mysql_result($result,0,"email");

echo '<p>' . $C1 . ' @ ' . $email . '</p>';
 
}

Im trying to select companyName,id, and business type from the companies table where the status is one

and I'm also trying to select the email from the companyAdmins table where the companyID is equal to the id from the companies table.

 

I want to basically get

 

companyName.companies, id.companies, businessType.companies, and email.companyAdmins

 

I have this file written and now I'm trying to go back and refactor the code and make everything cleaner.

[AbraCadaver]

Not tested:

SELECT companies.companyName, companies.id, companies.businessType, companyAdmins.email
FROM companies, companyAdmins
WHERE companies.id = companyAdmins.companyID AND companies.staus = 1

[thara]

Perhaps, You can use 'join'

 

Try this,

SELECT c.companyName, c.id, c.businessType, ca.email
FROM companies c
INNER JOIN companyAdmins ca ON
    c.id = ca.companyID
WHERE c.staus = 1