Welcome to WebmasterWorld Guest from 54.160.221.82

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Help with a MySQL query

     
3:43 am on Aug 13, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Jan 7, 2003
posts: 505
votes: 0


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.

6:30 pm on Aug 16, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 19, 2006
posts:92
votes: 0


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

3:56 am on Aug 17, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Jan 7, 2003
posts: 505
votes: 0


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 :-).

10:18 am on Aug 17, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 19, 2006
posts:92
votes: 0


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