mysql select query with join not showing the results of multiple rows

[Ebiso]

I am trying to create a search form for my database where a user can search for a customer name and all of that customers addresses will be displayed. my structure looks like this

Customer table

• ID
• First Name
• Last name
• Company Name

Address Table

• ID
• line 1
• post code
• town
• Customer_ID

Site Table

• ID
• Address_ID
• notes

I take either the first, last, or company name as an input($field) and store this as a variable along with which column they want to search in($query) then I use the following query to check the database for matching criteria

$data = mysql_query("SELECT *
     FROM customer 
     INNER JOIN address ON customer.ID = address.customer_ID
     INNER JOIN sites ON address.ID = sites.address_ID 
     WHERE customer.ID IN (SELECT customer.ID 
     FROM customer
     INNER JOIN address ON customer.ID = address.customer_ID 
     INNER JOIN sites ON address.ID = sites.address_ID
     WHERE upper(customer.$field) LIKE'%$query%')") ;

I print the results using

    while($results = mysql_fetch_array($data)){
            echo "<br>"; 
            echo $results['First_Name']; 
            echo " "; 
            echo $results['Surname']; 
            echo $results['town']; 
            echo " "; 
            echo $results['postcode'];

The problem occurs when one customer has multiple addresses. A home address and a site address that are different. The query will only print one of the addresses, the site address ( which is submitted second and seems to overwrite the home address)

in the address table both of these addresses contain the same Customer_ID, how can I get them to both be displayed rather than just one?

[smantscheff]

Your second clause is redundant.
In effect your query reads:

select * from customers where id in (...)

This can select at most one customer per id.

Instead use:

SELECT *
FROM customer 
INNER JOIN address ON customer.ID = address.customer_ID
INNER JOIN sites ON address.ID = sites.address_ID 
WHERE upper(customer.$field) LIKE'%$query%'