On Feb 26, 2007, at 4:35 PM, 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%' ) )
You're probably getting a cartesian product
Try it with a bunch of subqueries
SELECT customer.id, firstname, lastname
FROM customer
WHERE customer.id in ( select custid from email where email like '%
253%' )
OR customer.id in ( select custid from phone where phone like )
OR customer.id in ( select custid from address where
street like '%253%' or street2 like like '%253%' or city like '%253%'
or state like '%253%' or zip like like '%253%')
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
