Hello. > trying to write a query to find out whether there are any email >addresses in the first table that do not have a counterpart in the >second.
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. See: http://dev.mysql.com/doc/refman/5.0/en/join.html There is a good article as well: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html Dougal Watson wrote: > When I perform a particular query using a join the number of rows counted as > hits exceeds the number of rows returned. I think I know why but don't know > how to interrogate the database to confirm (and remedy) my suspicion. > > The basic database structure is two tables. Each table contains an email > address field. The join allows me to link a publication reference in the > first table, via the email address, to the author's name in the second. > > I suspect my problem results from there being a couple of email addresses in > the first table that do not have a counterpart in the second. I've been > trying to write a query to find out whether there are any email addresses in > the first table that do not have a counterpart in the second. > > My follow-on question is how do I write my original query so that if there's > no corresponding email address in the second table, a result is still > returned? > > My basic query is of the form: > $query = "SELECT datetime, author_email, title, body, author.person AS > author_name > FROM $table01, $table02 > WHERE > ( > ( > ((body REGEXP '$searchstring01') > OR > (title REGEXP '$searchstring01')) > $choice3 > ((body REGEXP '$searchstring02') > OR > (title REGEXP '$searchstring02')) > $choice4 > ((body REGEXP '$searchstring03') > OR > (title REGEXP '$searchstring03')) > ) > AND > ((YEAR(datetime) >= '$startyear') AND (YEAR(datetime) <= > `$endyear')) > AND > (postings.author_email = author.email) > ) > ORDER BY datetime DESC > > I also perform a COUNT query but that query doesn't include the join, just > the hits against the searchstrings. I've tried the COUNT with the join but > it returns and error. > > I'm sorry if these are very basic, MySQL 101, questions ... I am a very > basic recreational coder :-) > > Cheers > Dougal > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
