I'm not sure, but I think "FROM customer,address,phone,email" forms the cartesian product of those four tables before it searches. I think you just need
SELECT customer.id, firstname, lastname FROM customer WHERE ... Your where conditions form an implicit join between the tables, so you don't have to enumerate them up front. Tim > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > Ryan Dary > Sent: Monday, February 26, 2007 3:36 PM > To: REALbasic NUG > Subject: REALSQLDatabase query returns more rows than I wanted > > > 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> > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.446 / Virus Database: 268.18.3/699 - Release Date: > 2/23/2007 1:26 PM > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.3/699 - Release Date: 2/23/2007 1:26 PM _______________________________________________ Unsubscribe or switch delivery mode: <http://www.realsoftware.com/support/listmanager/> Search the archives: <http://support.realsoftware.com/listarchives/lists.html>
