Help with a MySQL query

[NickCoons]

I have a database with several tables and I'm working on searching the table, but I'm including other tables in the search. For instance, I'm searching "claims", which contains claims against entries listed in the "debtors" table. There is a "debtor_phones" table which stores the phone numbers associated with the debtors, where each debtor may have zero or more phone numbers.

Here is my query:

SELECT claims.id, claims.claim, clients.name, debtors.company, debtors.last_name, debtors.first_name, debtors.middle, debtors.ssn, claim_status.status, claim_types.type
FROM claims, clients, debtors, debtor_phones, claim_status, claim_types
WHERE clients.id = '$client_id' AND
 claims.status_id = '$status_id' AND
 claims.type_id = '$type_id' AND
 INSTR(debtors.company, '$debtor_company') > 0 AND
 INSTR(debtors.last_name, '$debtor_last_name') > 0 AND
 INSTR(debtors.first_name, '$debtor_first_name') > 0 AND
 INSTR(claims.claim, '$claim_number') > 0 AND
 INSTR(debtors.ssn, '$debtor_ssn') > 0 AND
 INSTR(debtor_phones.phone, '$debtor_phone') > 0 AND
 claims.client_id = clients.id AND
 claims.debtor_id = debtors.id AND
 debtors.id = debtor_phones.debtor_id AND
 claims.status_id = claim_status.id AND
 claims.type_id = claim_types.id
ORDER BY claims.claim, debtors.last_name

It's not exactly as above.. some of the query is created by PHP if() statements depending on what search terms were used on the form.

So far, this has worked well for me. Here's the problem. If a debtor has one phone number in debtor_phones, then it shows the claim once, just as it should. But if the debtor has zero phone numbers, the claim isn't listed at all. If the debtor has two phone numbers, the claim is listed twice. Presumably, the claim is listed equal to the number of phone numbers the debtor has in debtor_phones.

I know exactly why this happens. I just don't know how to fix it. I want it to list matching claims once regardless of the number of phone numbers the debtor has listed.

[darrenG]

Yes, this is because you are joining the tables up with the equivalent of an INNER JOIN, which only returns a result if there is a match across the joined tables. If there is no match, it will return no rows.

Further, you are returned multiple results when many phone no.s exist because MySql the relationship is one-to-many and MySQL doesn't know which row is the one you want.

first then, you need to specify the join in which the phone table is involved in as a LEFT JOIN, where the phone table is on the right. This will return a row regardless if there is a match.

To stop the repeated rows, you will likely either have to use an aggregate funtion, coupled with a group by clause, or, use a sub-query to retrieve the relevant phone number.

Off the top of my head, I think that if you want all of the phone numbers, you will simply be forced to have a row for each phone number, but someone better with SQL may be able to suggest otherwise.

Hope that helps

[NickCoons]

Thanks for the response. I'm actually not interested in retrieving any of the phone numbers in this particular query. I want it to retrieve the claims where there is a matching (or partially matching) phone number. The phone number(s) is/are actually retrieved later when someone chooses to view one of the matching claims.

Hopefully that makes it easier :-).

[darrenG]

It does, remove the link to the phone number table all together!