I’m stuck with a MySQL join operation and getting NULL values where I shouldn’t. I have two tables that need to be connected properly.
Here are my table structures:
connections | user_id | friend_id
profiles | id | name
What I want to achieve: Get all friendships from the connections table where a specific ID appears in either user_id or friend_id column, then join with profiles table to fetch the actual names.
The issue is that when I join the profiles table, the name field returns NULL instead of the actual username values.
My current query looks like this:
SELECT name
FROM (
`user_connections`
)
LEFT JOIN `profiles` ON ( 'profiles.id' = 'friend_id'
OR 'profiles.id' = 'user_id' )
WHERE `user_id` = 1
OR `friend_id` = 1
Can someone help me figure out what’s wrong with this join? I feel like I’m missing something obvious but can’t spot the error.
Yeah, the single quotes around column names are definitely breaking it, but there’s another issue. Using OR in your JOIN makes it unclear which profile record to match - you’ll get messy results.
Restructure it with a UNION instead:
SELECT p.name
FROM user_connections c
JOIN profiles p ON (c.friend_id = p.id)
WHERE c.user_id = 1
UNION
SELECT p.name
FROM user_connections c
JOIN profiles p ON (c.user_id = p.id)
WHERE c.friend_id = 1
This handles both cases cleanly - when your target user shows up as user_id or friend_id. Each JOIN stays simple with a clear one-to-one match.
you’re using single quotes around column names, which treats them as strings instead of actual columns. change ‘profiles.id’ to profiles.id without quotes, and do the same for your other column references. that’s why you’re getting nulls - mysql can’t match string literals to actual data.