目前,我有以下两个查询:
"SELECT company.id, company.name,
contact.firstname, contact.lastname,
contact.email, contact.id AS contactid
from ecampaign_lookup
LEFT JOIN company ON ecampaign_lookup.companyid=company.id
LEFT JOIN contact ON ecampaign_lookup.contactid=contact.id
WHERE ecampaign_lookup.campaignid=".$tid;
和:
"SELECT company.id, company.name,
contact.firstname, contact.lastname,
contact.email, contact.id AS contactid
from ecampaign_lookup
LEFT JOIN company ON ecampaign_lookup.companyid=company.id
LEFT JOIN contact ON contact.companyid=company.id
WHERE ecampaign_lookup.campaignid=".$tid."
AND contact.defaultcontact=1";
不同之处在于,第一个查询从“ ecampaign_lookup”表返回联系人,第二个查询从“ company”表返回联系人。第二个查询还要求仅选择'contact.defaultcontact = 1'的联系人,因为每个公司有很多联系人,但每个ecampaign_lookup只有一个联系人。
目前,我分别运行这两个查询,然后使用for循环比较结果,如果一个查询的返回值小于或大于另一个查询,则很容易出错:
for ($x=0;$x<count($query1_result);$x++){
if ($query1_result[$x]['contactid']!=$query2_result[$x]['contactid']){
echo $query1_result[$x]['firstname']." has been replaced by ".
$query2_result[$x]['firstname'];
}
}
如何将两个查询合并在一起,以将ecampaign_lookup.contactid与第二个查询中的contact.id进行比较,并且仅返回两个不匹配的结果?
好的,经过反复尝试,我找到了使用子查询的解决方案:
SELECT company.id,
company.NAME,
contact.firstname,
contact.lastname,
contact.email,
contact.id AS contactid
FROM ecampaign_lookup
LEFT JOIN company
ON ecampaign_lookup.companyid = company.id
LEFT JOIN contact
ON contact.companyid = company.id
WHERE ecampaign_lookup.campaignid = :tid
AND contact.defaultcontact = 1
AND contact.id NOT IN (SELECT contact.id AS contactid
FROM ecampaign_lookup
LEFT JOIN company
ON
ecampaign_lookup.companyid = company.id
LEFT JOIN contact
ON
ecampaign_lookup.contactid = contact.id
WHERE ecampaign_lookup.campaignid = :tid)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句