CODEWITHSUNDEEP
phpmysql
dlofrodloh
dlofrodloh

Reputation: 1744

How to merge these two queries into a comparison query

Currently I have these two queries: 

"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;

And:

"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";

The difference is the first query returns contacts from the 'ecampaign_lookup' table and the second returns contacts from the 'company' table. The second query also has the requirement of only selecting contacts where 'contact.defaultcontact=1' as there are many contacts per company but only one contact per ecampaign_lookup.

At the moment I'm running these two queries separately and then using a for loop to compare the results, which will be prone to errors if one query returns less or more than the other:

        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'];   
                }
        }

How can I merge the two queries together to compare ecampaign_lookup.contactid with the contact.id in the second query and only return results where the two don't match?

Upvotes: 1

Views: 48

Answers (2)

dlofrodloh
dlofrodloh

Reputation: 1744

Ok, after much trial and error I found a solution using a subquery:

 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)

Upvotes: 0

GiDo
GiDo

Reputation: 1340

To compare two query, UNION ALL and the HAVING clause it the key.

This example should work and return only duplicate rows.

SELECT id, name, firstname, lastname, email, contactid
FROM
(
  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

  UNION ALL

  SELECT cp2.id, cp2.name, 
     ct2.firstname, ct2.lastname, 
     ct2.email, ct2.id AS contactid
  FROM ecampaign_lookup ecl2
  LEFT JOIN company cp2 ON ecl2.companyid=cp2.id 
  LEFT JOIN contact ct2 ON ct2.companyid=cp2.id
  WHERE ecl2.campaignid=:tid AND ct2.defaultcontact=1
)
GROUP BY id, name, firstname, lastname, email, contactid
HAVING count(*) >= 1

Upvotes: 4

Related Questions