mysql outer join with where

Question

I have a database where users must have a username and on another table have an optional contact_name that is given to them by each user.

For example:

user A might call user B 'FOO'

but user C calls user B 'BAR'

and user D might not have given user B a contact_name

so it should return NULL.

The query below works in getting me all the usernames and contact_names even when NULL which is the desired result.

SELECT Login.username, PhoneNumber.phone, AddressBook.contact_name FROM Login
LEFT OUTER JOIN PhoneNumber ON Login.id = PhoneNumber.user_id
LEFT OUTER JOIN AddressBook ON PhoneNumber.phone = AddressBook.phone

However, I only want to return WHERE AddressBook.user_id = 67 which will return the usernames and contact_names that User A is associated with.

Currently when I add WHERE AddressBook.user_id = 67 it doesn't return any of the contact_names with NULL values which is expected but I need those rows with NULL and without NULL contact_names.

How do I go about this?

Thanks

Linger · Accepted Answer · 2014-06-03 17:31:43Z

3

How about adding AddressBook.user_id = 67 to the JOIN:

SELECT l.username, p.phone, a.contact_name 
FROM Login l
LEFT OUTER JOIN PhoneNumber p ON l.id = p.user_id
LEFT OUTER JOIN AddressBook a ON p.phone = a.phone AND a.user_id = 67

answered Jun 3, 2014 at 17:31

Linger

15.1k23 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hituptony · Accepted Answer · 2014-06-03 17:32:06Z

0

SELECT Login.username, PhoneNumber.phone, AddressBook.contact_name FROM Login
LEFT OUTER JOIN PhoneNumber ON Login.id = PhoneNumber.user_id
LEFT OUTER JOIN (SELECT contact_name, phone, user_id from AddressBook where user_ID = 67 order by 1,2,3)ON PhoneNumber.phone = AddressBook.phone

I think this would be sufficient. Make your second left join only return the rows with 67 as user id. but honestly without knowing your relationships, it's hard to tell.

Is phone number really the only field you can join on?

answered Jun 3, 2014 at 17:32

Hituptony

2,8703 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

mysql outer join with where

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related