使用左连接获取记录

I try to get the records from opportunity that created manually, so it means it’s not created via Converted from Leads or other module.

I’m trying to do is to get the opportunity record that is not converted from leads.

Below, you will see my query using left join the leads to opportunity using opportunity id from leads table and the opportunity id from Opportunity table.

But whenever I try to run this query it doesn’t show the records that created manually in the opportunity, I just want to get the records that are not converted and created manually in the opportunities. May I know why it is not showing? Thanks guys.

$strQuery = " SELECT 
                *
             FROM
                leads l
            LEFT JOIN 
                opportunities O
            ON
                l.opportunity_id = O.id
            WHERE
                l.deleted = '0'
            AND
                O.deleted = '0'
            AND
                l.converted = '0'
            AND
                DATE_FORMAT(O.date_created, '%y-%m-%d') = CURDATE()";
//
$hQuery = $db->query($strQuery);
//
while ( $arRow = $db->fetchByAssoc($hQuery) ){

// My logic
}

I just want to get the records that are not converted and created manually in the opportunities

You may need to reverse the table relationships, e.g.

SELECT *
FROM opportunities O
LEFT JOIN leads l ON O.id = l.opportunity_id
WHERE l.opportunity_id IS NULL
AND O.deleted = '0' 
AND DATE_FORMAT(O.date_created, '%y-%m-%d') = CURDATE()

here there is no assocated "lead", just an "opportunity".