homepage Welcome to WebmasterWorld Guest from 54.205.247.203
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Help with a MySQL query
NickCoons




msg:3721650
 3:43 am on Aug 13, 2008 (gmt 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.

 

darrenG




msg:3724572
 6:30 pm on Aug 16, 2008 (gmt 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

NickCoons




msg:3724746
 3:56 am on Aug 17, 2008 (gmt 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 :-).

darrenG




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved