I'm probably making a mistake that has been discussed here before, but I
couldn't find anything in the archives that seemed to be the same.
I'm trying to help someone with a database project their writing for a
simple address book. Here is the table structure:
Table: customer
Fields: id, firstname, lastname
Table: email
Fields: id, custid, email
Table: phone
Fields: id, custid, phone
Table: address
Fields: id, custid, street, street2, city, state, zip
Now, they're trying to have a simple search to get the id, firstname and
lastname for a given search phrase, searching all the tables and fields.
Here is the query that is currently being used:
SELECT customer.id, firstname, lastname FROM
customer,address,phone,email WHERE( customer.firstname LIKE '%253%' or
customer.lastname LIKE '%253%') or ( customer.id = address.custid AND (
address.city LIKE '%253%' or address.state LIKE '%253%' or
address.street LIKE '%253%' or address.street2 LIKE '%253%' or
address.zip LIKE '%253%') ) or ( customer.id = phone.custid AND
(phone.phone LIKE '%253%' ) ) or ( customer.id = email.custid AND (
email.email LIKE '%253%' ) )
The basic "organizational thought" for this query was that each main
component:
(customer.id = <table_name>.custid AND ( <field_name> LIKE
<search_phrase> or ... )
Would be sufficient to search the necessary tables.
In the above query, you'll see '%253%' because in this case, for
demonstration, it is as if someone is searching through the database for
the phrase "253" which happens to be a phone area code for one of the
contacts.
Now, what is happening, is that many rows, about 25, of data are being
returned. This is not what we wanted to happen. Of 5 records, 1 of
them contain the phrase "253" in the phone field, so we would have
wanted to just see this record come back representing that one record,
not 25.
If anyone could shed some light on this issue, I'd greatly appreciate
it. I haven't used the database for a while, and am not sure what the
problem is with the query.
Thanks,
Ryan Dary
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
|