列表不在内部联接中的ID

Question

I have a SELECT statement that provides me with a list of relevant IDs and company names that match the criteria. Here is the statement:

$result = mysqli_query($con,"SELECT a.OWNER_ID,b.ID, b.TITLE AS companyname FROM b_crm_act a INNER JOIN b_crm_company b ON a.OWNER_ID = b.ID");

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['ID'] . "</td>";
echo "<td>" . $row['companyname'] . "</td>";
echo "</tr>";
}
echo "</table>";

This lists all the IDs and company names. What I actually need is a list of IDs from b_crm_company that aren't in b_crm_act. I'm thinking I use NOT IN function but I've tried this and it's not listing anything.

Your help would be appreciated.

Answer 1

you can use LEFT JOIN on this

SELECT  a.ID, 
        a.TITLE AS companyname 
FROM    b_crm_company a 
        LEFT JOIN b_crm_act b 
            ON a.ID = b.OWNER_ID // <== specify how the tables are linked
WHERE   b.OWNER_ID IS NULL       // <== a value of NULL when it doesn't exists

Not sure about your column names but this is how it looks like.