MySQL: Complex Join Statement involving two tables and a third correlation table
- by Stephen
I have two tables that were built for two disparate systems. I have records in one table (called "leads") that represent customers, and records in another table (called "manager") that are the exact same customers but "manager" uses different fields
(For example, "leads" contains an email address, and "manager" contains two fields for two different emails--either of which might be the email from "leads").
So, I've created a correlation table that contains the lead_id and manager_id. currently this correlation table is empty.
I'm trying to query the "leads" table to give me records that match either "manager" email field with the single "leads" email field, while at the same time ignoring fields that have already been added to the "correlated" table. (this way I can see how many leads that match have not yet been correlated.) Here's my current, invalid SQL attempt:
SELECT leads.id, manager.id
FROM leads, manager
LEFT OUTER JOIN correlation ON correlation.lead_id = leads.id
WHERE correlation.id IS NULL
AND leads.project != "someproject"
AND (manager.orig_email = leads.email OR manager.dest_email = leads.email)
AND leads.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-10 23:59:59'
ORDER BY leads.created ASC;
I get the error: Unknown column 'leads.id' in 'on clause'
Before you wonder: there are records in the "leads" table where leads.project != "someproject" and leads.created falls between those dates. I've included those additional parameters for completeness.
