This is the query that was given to me: SELECT e.*, c.* FROM correspondence e, certificate c, logins l WHERE c.void <> 1 AND c.uid = l.uid AND l.parent = "wd001" AND e.invoice_num = c.invoice_num AND e.to_uid = "wd001" AND e.actioned = 0 ORDER BY e.message_num
Pretty straight forward. Perhaps not as optimized as it could be, but that's not the issue at hand (so please don't dwell on that. :p) Note: invoice_num in the correspondence table is either 0 (Zero) or any other number that is not zero. However, invoice_num in the certificate table is always any other number that is not zero. Taking the above note into consideration, that query will return only those correspondence records that has an invoice_num of not zero. That's not what I want. I want a query that will also return those correspondence records that have an invoice_num of zero. So I've been fooling around with try to get the query to work as a left join. However, I've been failing miserably and am hoping someone out there might be able to help me out and tell me where I'm going wrong. This is what I've been able to come up with: SELECT e.* FROM correspondence e LEFT JOIN certificate c ON c.invoice_num = e.invoice_num AND c.void <> 1 AND logins.uid = c.uid AND logins.parent = "wd001" WHERE e.to_uid = "wd001" AND e.actioned = 0 ORDER BY e.message_num\G I want all correspondence records returned that match the WHERE but I also what the certificate data included where the qualifications after the LEFT JOIN are true. Help? Chris --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
