> It seems like a job for a LEFT JOIN. To see the records which
> are present in table A and not present in table B use this query:
>
> SELECT A.*
> FROM A
> LEFT JOIN B
> USING(common_field)
> WHERE B.common_field is NULL.
Thanks Jeb,
I¹ve been working with this idea this evening and it¹s worked well for both
the main search engine and the diagnostic effort to find the missing
B.common_field entries.
The search engine query is now
SELECT datetime, title, body, author.person AS author_name
FROM publication
LEFT JOIN author
USING (email)
WHERE ( [WHERE DETAILS DELETED] )
ORDER BY datetime DESC
and this returns all the hits, irrespective of whether there is a
corresponding email address in author.email, my B.common_field.
The textbook chapter you referenced now has me working on some ³IFNULL²
options to try and fill-in the gap for future missing values. I've used your
NULL suggestion above and identified the (six) problem rows ... now I'm off
to fix them.
Your short response has helped me solve a troubling problem, taught me a
heap about joins, and now has me off chasing further knowledge about using
MySQL flow control rather than relying on the PHP to tidyup unexpected
values ... From my perspective a very, very valuable few words. Thanks very
much for taking the time.
Cheers
Dougal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
