Right. If the employee ID in either the rep_no or entered_by columns does
not have a corresponding row in the global_employee table, then the regular
join won't match that row. In that case, as you found, you need a LEFT
JOIN, which guarantees you get the rows from the table on the left, and
auto-creates NULL fields for the table on the right when it has no matching
row. For reference, this is mentioned in the manual
<http://dev.mysql.com/doc/mysql/en/JOIN.html>.
Michael
Michael J. Pawlowsky wrote:
Thanks a lot Michael.
A regular join did not seem to work. But when I tried a LEFT JOIN it
worked.
A cut down example of it is the following.
SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as
marketing_name
FROM global_lead
LEFT JOIN global_employee es ON global_lead.rep_no = es.id
LEFT JOIN global_employee em ON global_lead.entered_by = em.id
WHERE global_lead.rep_no = 8
Michael Stassen wrote:
You need to join the employee table twice, once for each id lookup,
like this:
SELECT es.name AS sales_name, em.name AS marketing_name, leads.id
FROM leads JOIN employee es ON leads.salesid = es.id
JOIN employee em ON leads.marketingid = em.id;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
