I am attempting to left join several tables. This was working at one time
but has seemed to stop just recently. I have checked and installed the
lastest version of mysql via Debian Etch apt-get. I am running version
5.0.32.
I have simplified the example down to just 3 tables but the problem exists
at this point.
Table 'contacts' - Does not matter what the fields are, still the same
problem. I am not using TEXT fields though. Most are int's or varchar's
account_num
first_name
last_name
Table 'address'
account_num
address_1
address_2
city
state
zip
Table 'phone'
account_num
phone_1
phone_1_type
phone_2
phone_2_type
What I want to do is search all three tables for something, return anything
that matches. So here is the select statement I have been using:
SELECT contacts.account_num, first_name, last_name, address_1, city_1,
phone_1 FROM contacts LEFT JOIN (address, phone) ON (contacts.account_num =
address.account_num AND contacts.account_num = phone.account_num) WHERE
contacts.account_num LIKE '%something%' OR contacts.first_name LIKE
'%something%' OR address.address_1 LIKE '%something%' OR
address.address_2LIKE '%something%' OR
address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR
phone.phone_2 LIKE '%something%' ORDER BY last_name;
When I run this query I only get data back from the 'contacts' table. What
I have been able to track down is that if I am missing data from any of the
tables that I LEFT JOIN'd then all the data from all the LEFT JOIN'd tables
will be NULL. In other words if I have account data in tables 'contacts'
and 'address' but nothing in 'phone' then no data from tables 'address' or
'phone' will be returned. If I add data to 'phone' then data is returned
properly.
Is this correct behavior? If so, any suggestions on how to solve this
problem would be great. Realize this is a smaller example of what I am
really trying to do. There are at least 4 tables in the select statement at
any one time and could be as many as 6.
Thanks!
--
Wes Hegge
- If the phone rings. Its not me.
-- Jimmy Buffet
