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>
