Getting related data from an uncertain column

Question

I am working on a friendship logic, the table friendship has this structure:

`friendship_id` INT NOT NULL ,
 `user1_id` INT NOT NULL ,
 `user2_id` INT NOT NULL ,
 `status` INT NULL ,
 `created` DATETIME NULL ,
 `message` VARCHAR(255) NULL ,

And the profile table.

`profile_id` INT NOT NULL ,
 `user_id` INT NOT NULL ,
 `first_name` VARCHAR(255) NULL ,
 `last_name` VARCHAR(255) NULL ,
 `image_url` VARCHAR(255) NULL ,
 `gender` INT NULL ,
 `city_id` INT NULL ,
 `country_id` INT NULL ,

I am trying to get a list of the the friends of a certain user, but I want related info also, for example their city.

The problem is the user_id could be either in user1_id or user2_id.

I can try with a left join but the results are not ok:

select
 city.name,
 p1.first_name as 'user1_first_name',
 p1.last_name as 'user1_last_name',
 p1.user_id as 'user1_id',
 p1.profile_id as 'user1_profile_id',
 p1.image_url as 'user1_image',
 p2.first_name as 'user2_first_name',
 p2.last_name as 'user2_last_name',
 p2.user_id as 'user2_id',
 p2.profile_id as 'user2_profile_id',
 p2.image_url as 'user2_image'
 from friendship f
 left join profile p1 on p1.user_id = user1_id
 left join profile p2 on p2.user_id = user2_id
 left join city on p1.user_id = 1 and city.city_id = p1.city_id or city.city_id = p2.city_id
 where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

Note: Probably my sql is not the best thing you'll ever see :)

What's the best way to get the information?

Answer 1

It looks like some of your difficulty may be order of operations related. For example, your

where f.user1_id = 1 or f.user2_id = 1 and f.status = 2

is evaluated as

where f.user1_id = 1 or (f.user2_id = 1 and f.status = 2)

It may be more straightforward to start from the profile table of the user you're starting from :

from [profile] p1
left join city c
 on p1.city_id = c.city_id
left join friendship f
 on p1.[user_id] in (f.user1_id,f.user2_id)
 and f.[status] = 2
left join [profile] p2
 on p2.[user_id] in (f.user1_id,f.user2_id)
 and p1.[user_id] <> p2.[user_id]
where p1.[user_id] = 1