Yes, that is the correct behavior of a LEFT JOIN. A left join keeps
all the record from the original/left table and will link up any
related data from the right table, but if there is no related data,
it sets it to NULL. If you want the join to act as a filter, the just
use regular JOIN.
Brent
On Jan 8, 2008, at 12:24 PM, Wes Hegge wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
