[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