[thelist] mySQL question

[Andy Budd]

Hi folks,

I've a quick mySQL question. Say I have 3 tables. user_name, user_phone 
and user_mobile

user_id | name
======|=====
       1      |   bob
       2      |   jon
       3      |   nic
       4      |   sue

user_id | phone
======|=====
       1      |   01273
       3      |   0171
       4      |   0161

user_id | mobile
======|=====
       1      |   07880
       2      |   09910

I want to bring back the following


user_id | name  |  phone  |  mobile
======|======|=======|======
       1      |   bob    | 01273   |  07880
       2      |   jon     | null         |  09910
       3      |   nic     | 0171      |  null
       4      |   sue    | 0161      | null

I can use a NATURAL LEFT JOIN to join user_name with either user_phone 
or user_mobile. However If I do something like

SELECT * FROM user_name NATURAL LEFT JOIN (user_phone, user_mobile)

it doesn't work as every entry in user_mobile appears to be getting 
joined with every entry in user_phone

i.e. I get something like this

user_id | name  |  phone  |  mobile
======|======|=======|======
       1      |   bob    | 01273   |  07880
       2      |   jon     | 0171      |  07880
       3      |   nic     | null         |  07880
       4      |   sue    | 0161      |  07880
       1      |   bob    | 01273   |  09910
       2      |   jon     | 0171      |  09910
etc, etc

At the moment I'm having to do multiple lookups and then join them in 
PHP. However this just seems wrong to me. I feel mySQL should be able 
to do this. After all it seems like quite a simple, common thing to 
want to do. Any ideas would be much appreciated

Andy Budd

http://www.message.uk.com/