[thelist] mySQL question

kaoskoder at pandora.be kaoskoder at pandora.be
Wed Feb 11 06:18:50 CST 2004


hi,
no sql expert here, but i think this should work

SELECT un.user_id, un.name, up.phone, um.mobile
FROM user_name un, user_phone up, user_mobile um
WHERE un.user_id = up.user_id
and un.user_id = um.user_id

Nick.

Andy Budd wrote:
> 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/
> 





More information about the thelist mailing list