This is what JOIN syntax is for. It joins all of your tables together and then queries them as if they were one, instead of querying all tables for matching data.
Try SELECT DISTINCT customer.id, customer.firstname, customer.lastname FROM customer JOIN address ON customer.id = address.custid JOIN phone ON customer.id = phone.custid JOIN email ON customer.id = email.custid WHERE customer.firstname LIKE '%253%' OR customer.lastname LIKE '%253%' OR 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 phone.phone LIKE '%253%' OR email.email LIKE '%253%' - Tom On 27/02/2007, at 10:35 AM, Ryan Dary wrote: > 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> _______________________________________________ Unsubscribe or switch delivery mode: <http://www.realsoftware.com/support/listmanager/> Search the archives: <http://support.realsoftware.com/listarchives/lists.html>
