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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
