Welcome to WebmasterWorld Guest from 220.127.116.11
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.
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
Hopefully that makes it easier :-).