What is wrong with this JOIN query?

Question

I have a list of tenant reports, each link pointing to a report ID.

I need this query to select the report information, and some of the information related to the tenant who submit it.

This query gets the report information correctly, but returns copies of it with every tenant attached to it:

SELECT 
reports.person_reporting, reports.request_type, reports.details, tenants.office,
tenants.email, tenants.alt_email, tenants.office_phone, tenants.personal_cell,
tenants.emergency_phone, tenants.address, tenants.building_name
FROM reports, tenants
WHERE reports.id = '{$id}'
AND (
    tenants.email = reports.email
    OR tenants.alt_email = reports.alt_email
)

in that AND clause, I need it to match the tenant email address with the one contained in the report with the specified ID. But it's just getting every tenant with an email or alternate email that matches any email or alternate email in the reports (almost all of them, since most of them don't have an alternate email specified).

Is there any way for me to do this?

update

My problem was that there were lots of empty alt_email fields, and it was just picking all of them. The answers below were right in filtering out the empty fields, but I just changed my OR clause to an AND clause, which was more clear about how I wanted to match them anyways: they both have to match because there shouldn't be any duplicate emails in the database.

Answer 1

You should probably filter out the 'null' emails, like this.

AND (
    (tenants.email != '' AND tenants.email = reports.email) OR
    (tenants.alt_email != '' AND tenants.alt_email = reports.alt_email)
)

In reality, this seems like it ought to be a left join, i.e.:

SELECT 
reports.person_reporting, reports.request_type, reports.details, tenants.office,
tenants.email, tenants.alt_email, tenants.office_phone, tenants.personal_cell,
tenants.emergency_phone, tenants.address, tenants.building_name
FROM reports
LEFT JOIN tenants ON (
    (tenants.email != '' AND tenants.email = reports.email)
    OR (tenants.alt_email != '' AND tenants.alt_email = reports.alt_email)
)
WHERE reports.id = '{$id}'

This way, you will get the reports with no tenants at least once.

Answer 2

I'd assume that your problem is that there are a bunch of tenants with alt_email = NULL, and a bunch of reports with alt_email = NULL, and your OR clause will match each report with alt_email = NULL with all the tenants records with alt_email = NULL.

You should probably catch the NULL case:

WHERE reports.id = '{$id}'
AND (
  (tenants.email IS NOT NULL AND tenants.email = reports.email)
  OR (tenants.alt_email IS NOT NULL AND tenants.alt_email = reports.alt_email)
)